Running Total

[M_SBS_6]

Hi, 

I have a running total line chart which seems to be working if my x-axis is uses the Year and Month hierarchy. I see a continued growth.

My legend is a Column which has grouped a Product. 

However, if I change the x-axis to show just the month (as I want the total for all years Jan to Dec), I lose the cumulative view, it then just gives me the total for each month. 

Any idea how I can change this please? 

My measure:

Run_total=

Calculate(sum(prod[amt],

Filter( Allselected (prod),

Prod[date] <= max prod[date])))

[Ashish_Mathur]

Hi,

This approach should work.

1. Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name by the Month number column
2. Create a relationship (Many to One and Single) from the Date column of the Prod Table to the Date column of the Calendar Table
3. To your visual/slicers/filters, drag any date related field from the Calendar Table
4. Write these measures

Total = sum(Prod[amt])

Running total = calculate([Total],datesbetween(Calendar[date],minx(all(calendar),Calendar[date]),max(calendar[date])))

Hope this helps.

[v-junyant-msft · Accepted solution]

Hi @M_SBS_6 ,

@some_bih Thanks for your concern about this case!
Here is my sample data:

And I have tested your measure, but there is nothing wrong with it. Here are the test results:

Whether I use a hierarchy of dates or not, I can have cumulative totals appearing. 
If you are facing this situation:

You can try to view it by clicking on this button:

And after clicking it will change into this:

If your problem is still not solved, it would be helpful if you could provide us with your sample data, thank you!

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[some_bih]

Hi @M_SBS_6 based on your description and measure, it seems that in some of your measure DAX functin ALLSELECTED is used in definition (Allselected - you wrote above9.

Using ALLSELECTED, according to best practice is for visual. Still, if you define measure with ALLSELECTED  and create another measure which reference first measure with ALLSELECTED  in definition, the issue like unlogical results or hard to intepred result occur.

If above is case for your measures, when rewrite measures.