topic DATESINPERIOD All or Remove Filters in DAX Commands and Tips

DATESINPERIOD All or Remove Filters

JesperMadsen90 — Tue, 07 Feb 2023 11:31:35 GMT

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?

Re: DATESINPERIOD All or Remove Filters

Anonymous — Wed, 08 Feb 2023 07:51:00 GMT

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.

Re: DATESINPERIOD All or Remove Filters

JesperMadsen90 — Mon, 13 Feb 2023 07:28:08 GMT

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.