Forum Discussion
Cumulative Totals
Hi,
Assuming Date and Margin are columns already present in your source data table, perform the following steps:
- Create a Calendar Table
- Build a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of your Calendar Table
- To your Table/Matrix visual, drag Date from the Calendar Table
- Write these measures:
Total = sum(Data[Margin])
MTD Total = calculate([Total],datesmtd(calendar[date]))
Hope this helps.
Hi Ashish, I decided to group by date in power query as that was effectively what I was doing on the table visual in power BI. I was then able to use your method for cumulative totals on the daily target. Many thanks for your assistance with this, really appreciated
Hi Ashish, do you know if I could I use a similar approach for a 'daily cumulative total' as shown below? Happy to post this as a new topic if thats better?
Saying 'maximum' might be misleading here. The target value will be repeated for each date. So for example, all rows for 01/02/2022 will be 100.
Also the daily cumulative total would need to reset each month as shown.
Super UserHi,
Share the link from where i can download your PBI file.
Hi Ashish, I decided to group by date in power query as that was effectively what I was doing on the table visual in power BI. I was then able to use your method for cumulative totals on the daily target. Many thanks for your assistance with this, really appreciated
- Ashish_Mathur
You are welcome.