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

 

dateCummlative Sum 2020 Cummlative Sum 2015
2020 - June10050
2020 - July200100
2020 - August250150

 

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

 

dateCummlative Sum 2020 Cummlative Sum 2015
2020 - June100150
2020 - July200150
2020 - August250150
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

4 Replies

  • 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!

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    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