Running Total per week/month

[Nero_Carbone]

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

KR

[Nero_Carbone]

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?

[Greg_Deckler]

@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.

[Greg_Deckler · Accepted solution]

@Nero_Carbone Perhaps one of these can help:

(1) Better Running Total - Microsoft Fabric Community

Better Year to Date Total - Microsoft Fabric Community