Forum Discussion

vasu6811's avatar
vasu6811
Frequent Visitor
9 years ago

Get Running total upto Selected Month

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.

 

Need Help

1 Reply

  • v-sihou-msft's avatar
    v-sihou-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    vasu6811

     

    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,