Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Month over Month using a different static year ?

I am trying to write a cummlative sum for date range that looks like this   date Cummlative Sum 2020  Cummlative Sum 2015 2020 - June 100 50 2020 - July 200 100 2020 - August 250...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

    According to my understand , you wan to sum Amount based on each month of years dynamically ,right?


    In my opinion, you could use Matrix and Slicer like this:

    Or use the following formula:

    dateSlicer =
VAR _maxDate =
    MAX ( 'Periods'[Date] )
VAR _minDate =
    MIN ( 'Periods'[Date] )
RETURN
    CALCULATE (
        SUM ( Sales[Amount] ),
        FILTER (
            ALL ( Periods ),
            'Periods'[Date] >= _minDate
                && 'Periods'[Date] <= _maxDate
                && 'Periods'[Date].[Month] = MAX ( 'Periods'[Date].[Month] )
        )
    )

     

    Here is the pbix file.

     

    Did I answer your question ? Please mark my reply as solution. Thank you very much.
    If not, please upload some insensitive data samples and expected output.

     

    Best Regards,
    Eyelyn Qin

richbenmintz's avatar
richbenmintz
Icon for Resident Rockstar rankResident Rockstar
5 years ago

Hi Anonymous ,

 

Not really sure why you cannot use a filter on the Year and the Month, but I am not sure what your model looks like, assuming you have a Month column in your periods table you could try the formula below

 

var MinDate = DATE(2014,06,01)
var MaxDate = DATE(2015,05,31)
return CALCULATE(SUM(Sales[Amount]),
FILTER(ALL ( Periods[Date] ), (Periods[Date] <= MAX ( Periods[Date]) && Periods[Date] >= MinDate && [Date] <= MaxDate))&& Periods[Month] = MAX(Periods[Month]) )

 

Hope this Helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!