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.
Hi,
I have sales data for 2015, 2016 and 2017 year. I added a filter for YEAR and MONTH.
My scenario is, if I select Year I need to display running total only for selected year. If I select Month, I need to get upto selected month Running Total.
I am using following measure to get running total.
Cumm Total = CALCULATE (
SUM('Sales'[SalesTotal]),
FILTER ( ('Sales' ), 'Sales'[Date].[Date] <= max('Sales'[Date].[Date]) ),
VALUES ( 'Sales'[SalesTotal] )
)
Thanks in Advance.
I'm still not quite clear about your requirement. If your Running Total for selected year and selected month starts from the beginning of your table, you can try formula like below:
selected Cumulative Total = IF ( ISFILTERED ( 'Sales'[Year] ), IF ( ISFILTERED ( 'Sales'[Month] ), CALCULATE ( SUM ( 'Sales'[Amount] ), FILTER ( ALL ( 'Sales'[Date] ), 'Sales'[Date] <= EOMONTH ( DATE ( MAX ( 'Sales'[Year] ), MAX ( 'Sales'[Month] ), 1 ), 0 ) ) ), CALCULATE ( SUM ( 'Sales'[Amount] ), FILTER ( ALL ( 'Sales'[Date] ), 'Sales'[Date] <= DATE ( YEAR ( MAX ( 'Sales'[Year] ) ), 12, 31 ) ) ) ), CALCULATE ( SUM ( 'Sales'[Amount] ), FILTER ( ALL ( 'Sales'[Date] ), 'Sales'[Date] <= MAX ( 'Sales'[Date] ) ) ) )
If those calculations are based on corresponding year or month only, please try to change it into TOTALYTD() or TOTALMTD().
Regards,
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 |
---|---|
111 | |
95 | |
77 | |
69 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |