Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello PBI – Community, I hope you can help us with this problem.
We have a problem creating a measurement which cumulates profit for each months aka. running total measurement in our SuperStoreEU database. Basically we want to create a line chart where the Y-axis is sales profit and X-axis is months.
We have been troubleshooting with the following DAX measures sadly without any luck (https://community.powerbi.com/t5/Desktop/Cumulative-Line-Formula/m-p/22908😞
Cumulative Quantity := CALCULATE ( SUM ( Transactions[Quantity] ), FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) ) )
and this (https://community.powerbi.com/t5/Desktop/DAX-Running-Total-YTD/m-p/21908😞
Cumulative = VAR RowDate = Table1[Date] RETURN CALCULATE ( SUM ( Table1[Recurring] ); FILTER ( Table1; Table1[Date] <= RowDate && YEAR ( Table1[Date] ) = YEAR ( RowDate )
When we applied the following DAX measures, the measure worked, but it did not cumulate the profit for each months, hence making a running total .
In fact we tried to copy the exact same setup with the identical excel sheets from http://www.daxpatterns.com/cumulative-total/ without the same results. So do anyone of you have an alternative or experienced anything similar, which might hold the key to how you make a running total in PBI?
Thanks
Regards, David
Solved! Go to Solution.
Something similar to the following worked for me, but I needed a date table, where on my visual I used the month field from the date table for the axis, and I had a slicer that used year from the date table
Cumulative Quantity := CALCULATE ( SUM ( Transactions[Quantity] ), FILTER ( ALL ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) ) )
Thanks alot for your fast replies @blopez11 and @v-huizhn-msft
It turned out that the missing date table caused the problem. As @blopez11 mentioned, I needed to refer to a date table where I instead was working with a date field from the same table. I was only working with one table when the problem occured.
I am trying to reproduce your scenario. However, I get correct results without any issue. Could you please share more details for further analysis?
I also use the sample date in given link. Create a date table and create the relationship between them as follows.
I created the Cumulative Quantity and get the below screenshot. It still calculate the sum of each month day by day even when you select the month as slicer.
In addition, the calculated column also works fine. See following screenshots:
If you still have any problem, please feel free to ask.
Best Regards,
Angelia
Something similar to the following worked for me, but I needed a date table, where on my visual I used the month field from the date table for the axis, and I had a slicer that used year from the date table
Cumulative Quantity := CALCULATE ( SUM ( Transactions[Quantity] ), FILTER ( ALL ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |