Running Total

[pociners]

Hi Everyone,

I'm very new in PowerPivot and wanna ask something,
I have this table

and i create a calculation using this formula:

Running Total:=CALCULATE(SUM([Value]),FILTER(ALL(Table1[YearWeek]),[YearWeek]<=MAX([YearWeek])))

and indeed i can "running total" through YearWeek:

but if I "running total" through Year AND Week, the result is like this:

Can anybody help me? so if I "running total" through Year AND Week the result will be the same with "running total" through YearWeek.

Sorry for my bad english,

Thanks

 

[Ozeroth]

Hi pociners,

The problem is that FILTER( ALL(Table1[YearWeek]) ...) filters all values of YearWeek ignoring the filter context, but the filters on columns Year and Week remain.

Ideally, you should create a 'calendar' table (whether it is daily, weekly or whatever) that is separate from your fact table.
Your fact table should then be related to the key column of that table (looks like the key is YearWeek in your example).

Then you can use a formula that filters the entire Calendar table to compute the running total, like:

[B]Running Total :=[/B]
CALCULATE (
    SUM ( Table1[Value] ),
    FILTER ( ALL ( Calendar ), Calendar[YearWeek] <= MAX ( Calendar[YearWeek] ) )
)

(If the Calendar table key is Date, you can mark it as a Date Table and get away with FILTER ( ALL ( Calendar[Date] ) ... ), see here: Time Intelligence in Power BI Desktop - SQLBI )

However, if we stick with the table as in your example, you can achieve the same result with a more awkward formula:

[B]Running Total :=[/B]
CALCULATE (
    SUM ( Table1[Value] ),
    FILTER ( ALL ( Table1[Year], Table1[Week], Table1[YearWeek] ), Table1[YearWeek] <= MAX ( Table1[YearWeek] ) )
)

 

[pociners]

Hi pociners,

The problem is that FILTER( ALL(Table1[YearWeek]) ...) filters all values of YearWeek ignoring the filter context, but the filters on columns Year and Week remain.

Ideally, you should create a 'calendar' table (whether it is daily, weekly or whatever) that is separate from your fact table.
Your fact table should then be related to the key column of that table (looks like the key is YearWeek in your example).

Then you can use a formula that filters the entire Calendar table to compute the running total, like:

[B]Running Total :=[/B]
CALCULATE (
    SUM ( Table1[Value] ),
    FILTER ( ALL ( Calendar ), Calendar[YearWeek] <= MAX ( Calendar[YearWeek] ) )
)

(If the Calendar table key is Date, you can mark it as a Date Table and get away with FILTER ( ALL ( Calendar[Date] ) ... ), see here: Time Intelligence in Power BI Desktop - SQLBI )

However, if we stick with the table as in your example, you can achieve the same result with a more awkward formula:

[B]Running Total :=[/B]
CALCULATE (
    SUM ( Table1[Value] ),
    FILTER ( ALL ( Table1[Year], Table1[Week], Table1[YearWeek] ), Table1[YearWeek] <= MAX ( Table1[YearWeek] ) )
)

Aaaahhh Thank you, your 2nd formula works very well,
Well, Actually i have a separate table and related to my fact table, but when i run your first formula, it always goes wrong.
I think your 2nd formula is enough for me.
Thanks btw..