12M rolling sum where sum takes into consideration months which are not selected from slicer

[FatherTheWizard]

Hi,

I have used Quick Measure Rolling Average to create 12m rolling sum (replaced the averagex with sumx in the DAX). My problem is that currently on my report I have filtered months 1-4 from January because these are the months that are fully closed this year but the rolling 12m does not take into account the previous year's figures at all because those are not filtered.

How could I solve this?

Example current results and wanted results:

- Revenue is 100 per month and we have results for 1/2018-4/2019.

- Selected months are 1-4/2019

- Rolling 12m revenue with current DAX: 1/2019 = 100, 2/2019 = 200, 3/2019 = 300, 4/2019 = 400

- Rolling 12m revenue what we want: 1/2019 = 1200, 2/2019 = 1200, 3/2019 = 1200, 4/2019 = 1200

Current DAX:

External Revenue L12M (ACT) =

IF(

    ISFILTERED('(dim) 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('(dim) Date'[date].[Date])

    VAR __DATE_PERIOD =

        DATESBETWEEN(

            '(dim) Date'[date].[Date];

            STARTOFMONTH(DATEADD(__LAST_DATE; -12; MONTH));

            ENDOFMONTH(DATEADD(__LAST_DATE; 1; MONTH))

        )

    RETURN

        sumX(

            CALCULATETABLE(

                SUMMARIZE(

                    VALUES('(dim) Date');

                    '(dim) Date'[date].[Year];

                    '(dim) Date'[date].[QuarterNo];

                    '(dim) Date'[date].[Quarter];

                    '(dim) Date'[date].[MonthNo];

                    '(dim) Date'[date].[Month]

                );

                __DATE_PERIOD

            );

            CALCULATE([External Revenue (ACT)]; ALL('(dim) Date'[date].[Day]))

        )

)

[v-xicai]

Hi  @FatherTheWizard ,

Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case.

Best regards

Amy Cai

[Anonymous]

Hello @v-xicai ,

I have the same issue but on a rolling average ;  I've triend your solution on a rolling average but it does not work, is that normal ?

Alice

[v-xicai · Accepted solution]

Hi @FatherTheWizard ,

You can try create measures like DAX below, assuming [External Revenue (ACT)]= SUM('(dim) Date'[revenue])  

Period End = LASTDATE('(dim) Date'[date])

Period Start= FIRSTDATE( DATESINPERIOD('(dim) Date'[date], [Period End], -12, MONTH))

External Revenue L12M (ACT) =CALCULATE([External Revenue (ACT)],DATESBETWEEN ( '(dim) Date'[date], [Period Start], [Period End] ))

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.