cancel
Showing results for
Did you mean:
Frequent Visitor

Need a help - Calculate the running total in item level month over month

Hey all,

I know how to do cumulative calculation when putting date in row. But this time, i need to calculate the running total in item level (row) month over month (column)

See below example. the 2020-08 cumulative demands should be 44 for product A. Considering the 2020-09 demands is 103, so the cumulative demands by the end of 2020-09 should be 147...

This is my dax, but it's not correct 😞 Would you let me know your thoughts? Thanks!

Cumulative Demands = CALCULATE(CALCULATE([Demands],FILTER(ALLSELECTED('Date'[Date]),'Date'[Date]<=MAX('Date'[Date]))),FILTER(ALL('Fact'),COUNTROWS(FILTER('Fact',EARLIER('Fact'[Item])='Fact'[Item] && EARLIER('Fact'[Date])='Fact'[Date]))))

1 ACCEPTED SOLUTION
Super User IV

@naoyixue1 , Try like

Cumulative Demands = CALCULATE([Demands],FILTER(ALL('Fact'),COUNTROWS(FILTER('Fact',Max('Fact'[Item])='Fact'[Item] && MAX('Fact'[Date])='Fact'[Date]))))

or

Cumulative Demands =CALCULATE([Demands],FILTER(ALLSELECTED('Date'[Date]),'Date'[Date]<=MAX('Date'[Date])),FILTER(ALL('Fact'),COUNTROWS(FILTER('Fact',Max('Fact'[Item])='Fact'[Item] ))))

Proud to be a Super User!

2 REPLIES 2
Super User IV

@naoyixue1 , Try like

Cumulative Demands = CALCULATE([Demands],FILTER(ALL('Fact'),COUNTROWS(FILTER('Fact',Max('Fact'[Item])='Fact'[Item] && MAX('Fact'[Date])='Fact'[Date]))))

or

Cumulative Demands =CALCULATE([Demands],FILTER(ALLSELECTED('Date'[Date]),'Date'[Date]<=MAX('Date'[Date])),FILTER(ALL('Fact'),COUNTROWS(FILTER('Fact',Max('Fact'[Item])='Fact'[Item] ))))

Proud to be a Super User!

Frequent Visitor

Thanks @amitchandak Both ways work. Really appreciate your effort.

Announcements

Happy New Year from Power BI

This is a must watch for a message from Power BI!