DATESINPERIOD All or Remove Filters

[JesperMadsen90]

Hi!

I'm trying to calculate running 12 months total with the following measure:

MOVING TOTAL R12 =

        VAR OneYearBack =

            DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -1, YEAR )

        VAR Result =

            CALCULATE (

                VAR NumberOfMonths = COUNTROWS ( SUMMARIZE ( 'CRM Data', 'Date'[Year Month Number] ) )

                VAR NoOfCustomers = [No of Customer]

                VAR Result   = IF ( NumberOfMonths = 12, NoOfCustomers)

                RETURN

                    Result,

                OneYearBack

            )

        RETURN

            Result

Everything work fine except for that the measure can not stretch outside the months thats within my filtered table. Some sort of ALL formula seem to be needed. The result is accoring to below picture (table), the model contains data from 2019 but when I filter the table for 2020-2021 the "rolling 12 months measure" cannot get data from 2019.

Is there some way to get the measure to include dates outside the filtered data within the DATESINPERIOD context?

[v-tangjie-msft · Accepted solution]

Hi @JesperMadsen90 ,

Please this measure:

MOVING TOTAL R12 =
        VAR OneYearBack =
            Filter(ALL('Date'[Date]), [Date]<=MAX ( 'Date'[Date] )&&[Date]>=EDATE(MAX ( 'Date'[Date] ),-12))
        VAR Result =
            CALCULATE (
                VAR NumberOfMonths = COUNTROWS ( SUMMARIZE ( 'CRM Data', 'Date'[Year Month Number] ) )
                VAR NoOfCustomers = [No of Customer]
                VAR Result   = IF ( NumberOfMonths = 12, NoOfCustomers)
                RETURN
                    Result,
                OneYearBack
            )
        RETURN
            Result

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[v-tangjie-msft · Accepted solution]

Hi @JesperMadsen90 ,

Please this measure:

MOVING TOTAL R12 =
        VAR OneYearBack =
            Filter(ALL('Date'[Date]), [Date]<=MAX ( 'Date'[Date] )&&[Date]>=EDATE(MAX ( 'Date'[Date] ),-12))
        VAR Result =
            CALCULATE (
                VAR NumberOfMonths = COUNTROWS ( SUMMARIZE ( 'CRM Data', 'Date'[Year Month Number] ) )
                VAR NoOfCustomers = [No of Customer]
                VAR Result   = IF ( NumberOfMonths = 12, NoOfCustomers)
                RETURN
                    Result,
                OneYearBack
            )
        RETURN
            Result

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[JesperMadsen90 · Accepted solution]

Thanks! I guess this measure will work as well. The solution was however very easy, the model had not connected my fact table to the date dimension table, creating the relationship solved everything.