Running total does not work on filter

Anything related to PowerPivot and DAX Formuale
Sjuul
Posts: 1
Joined: Fri Mar 26, 2021 11:54 pm

Running total does not work on filter

Postby Sjuul » Mon Sep 13, 2021 9:19 pm

I have a pivot table in which I want to list the hours from a period aswell as the running total.
I have managed to create the running total using the following formula:

Uren YTD:=CALCULATE(SUM(HOURS[Uren]);FILTER(ALL(Datum);Datum[Datum]<=MAX(HOURS[DATE_])&&Datum[Datum]<=MAX(HOURS[DATE_])))

Hours (=uren) are stored by date_ in table hours and Hours[date_] is linked in data model to date_table DATUM[Datum]

I don't want to/know how to use the inbuild time intelligence as we have a broken bookyear from oct-sep.
In the date_table I have a separate column with the bookyear for each date: Datum[boekjaar] as well as a separate column for the month (starting with 01-Okt, 02-nov etc).


The thing is that when I use a filter (column in pivot tabel) the year 20/21 does YTD not start with 14357 but includes the total balance from the previous year.

Rijlabels 19-20 20-21 19-20 20-21

01 - Okt 10181 14357 10181 275787
02 - Nov 21705 22876 31886 298663
03 - Dec 28265 29605 60150 328268
04 - Jan 26161 25936 86311 354204
05 - Feb 6630 12829 92941 367032
06 - Mrt 11884 16455 104825 383487
07 - Apr 14658 14059 119482 397546
08 - Mei 9846 9608 129329 407153
09 - Jun 28553 22620 157881 429774
10 - Jul 64071 57685 221952 487459
11 - Aug 25366 33639 247318 521099
12 - Sep 14113 3349 261430 524448
Eindtotaal 261430 263018 261430 524448

Hope anyone knows how to fix this so that running total is recalculated for each bookyear.
Thanks
Sjuul

Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 2 guests