topic Re: Running Total per week/month in DAX Commands and Tips

Running Total per week/month

Nero_Carbone — Fri, 30 Aug 2024 16:57:14 GMT

Hello,

I would like to have a measure in power pivot for the running total that behaves like the one in the normal pivot tables.

For instance, in the pivot table below the value are shown as running totals based on the week number (y axis).

The running total is based on the week number, and for example after w6 in January it`s still reported the maximum value for the month.

Here it's the most i managed to accomplish via power pivot:

Here the code for the measure:

ActualsRunningTotal:=TOTALYTD(SUM(Actuals[Volume]), Actuals[Date], ALL(Actuals))

Here the structure of the model, highlighted the fields i use in the pivot.

I didn`t quite manage to find online how to handle this case, any ideas? Should i adjust the relationship between tables or use different functions?

I`m doing it via power pivot because then i would need to combine the measure with other two, for which I only need the normal totals.

Thank you in advance, any help is very much appreciated

Re: Running Total per week/month

Greg_Deckler — Fri, 30 Aug 2024 17:37:12 GMT

Nero_Carbone Perhaps one of these can help:

(1) Better Running Total - Microsoft Fabric Community

Better Year to Date Total - Microsoft Fabric Community

Re: Running Total per week/month

Nero_Carbone — Sat, 31 Aug 2024 18:57:11 GMT

Thanks for leading me on the right direction Greg_Deckler !

In the end i managed to accomplish what i needed with the following:

ActualsRunningTotal:=SUMX(FILTER(ALLSELECTED(Actuals),

'Actuals'[Date (Month Index)]=MAX(Months[MonthNum])&&

Actuals[WeekID]<=MAX(WeekID[WeekID])&&

YEAR(Actuals[Date])=MAX(WeekID[Year])),Actuals[CU])

I didn`t need to adapt the structure of the data model.

EDIT:

One more question please: despite using ALLSELECTED, it seems like the values don`t change when additional filters are applied to the context (e.g. adding the suppliers on the rows).

Before filters:

After detailing supplier:

How can i fix it please?

Re: Running Total per week/month

Greg_Deckler — Sun, 01 Sep 2024 11:12:55 GMT

Nero_Carbone Difficult to say. I would make sure that the proper relationships exist first. Often, when you see repeated values in a table it is the result of a missing or incorrect relationship.