Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Current MTD plus previous months

Hi I need to create a DAX measure that calculates the current MTD plus the previous 6 full months. This is to use as a report-level filter. Could anyone help? Thanks!
Need Help
  • negi007's avatar
    negi007
    5 years ago

    Anonymous 

     

    in this case you can create a last 7 months rolling total which will ensure that you have previous full month and current months MTD values.

     

     Sales rolling Total =
    IF(
        ISFILTERED('Date'[Date]),
        ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
        VAR __LAST_DATE = ENDOFMONTH('Date'[Date].[Date])
        VAR __DATE_PERIOD =
            DATESBETWEEN(
                'Date'[Date].[Date],
                STARTOFMONTH(DATEADD(__LAST_DATE, -7, MONTH)),
                __LAST_DATE
            )
        RETURN
            SUMX(
                CALCULATETABLE(
                    SUMMARIZE(
                        VALUES('Date'),
                        'Date'[Date].[Year],
                        'Date'[Date].[QuarterNo],
                        'Date'[Date].[Quarter],
                        'Date'[Date].[MonthNo],
                        'Date'[Date].[Month]
                    ),
                    __DATE_PERIOD
                ),
                CALCULATE(SUM('table'[Sales]), ALL('Date'[Date].[Day]))
            )
    )
     
    to create the above dax, i used the quick measure to first create last 7 months rolling average measure and then change the averagex function to sumx function in the dax code.
     

     

     
amitchandak's avatar
amitchandak
Super User
5 years ago

Anonymous , With time intelligence with a measure like

 

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

 

Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-6,MONTH))

 

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.