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

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?