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.
     

     

     
negi007's avatar
negi007
Community Champion
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.