Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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,