Month over Month using a different static year ?

[Anonymous]

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	150

Is it possible to write a cummalative sum of a year over another year in dax without using Date add and so i can just define the year as exactly 2019. The reason why i dont want to use Date add or any other (Date - 5 years), is because it is dynamic and it will not be correct next year.

Is this possible in dax?

What i have done so far:

I have managed this calculation which gives a total but does not break down correctly for month

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))

But it looks like this (doesnt give monthly breakdown).

date	Cummlative Sum 2020	Cummlative Sum 2015
2020 - June	100	150
2020 - July	200	150
2020 - August	250	150

[v-eqin-msft · Accepted solution]

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

[amitchandak]

@Anonymous , Try like

 measure  =
 var MinDate = DATE(2014,06,01)
 var MaxDate = DATE(2015,05,31)
 return 
 calculate(SUM(Sales[Amount]), FILTER(All(Date), Date[DAte] <=Max(Date[date]) && Date[Date] >=MinDate && Date[Date] <= MaxDate))

I think datesytd should also work like

YTD Sales = CALCULATE(SUM(Sales[Sales]),DATESYTD('Date'[Date],"5/31"))
5 YTD back Sales = CALCULATE(SUM(Sales[Sales]),DATESYTD(dateadd('Date'[Date],-5,Year),"5/31"))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[Anonymous]

Thanks for the response,

Is there anyway to avoid using a dynamic calculations like Date add?

YTD Sales: CALCULATE (SUM (Sales [Sales]), DATESYTD ('Date' [Date], "5/31"))
5 YTD Overdue Sales: CALCULATE (SUM (Sales [Sales]), DATESYTD (dateadd (' Date '[Date], - 5, Year), "5/31"))

Instead of using Dateadd use just the year 2019?

[richbenmintz]

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!