Forum Discussion
Solved
Improving Dax Measure to be more precise
Hi, I have a measure which works however is not very precise. The idea is to calculate a 3 month rolling average starting from PREVIOUS month, so if we are in February the average will be based...
We can calculate the dates in the range then use that as a filter like this:
!!INC RAG = VAR _Months = 3 VAR _Start = EOMONTH ( TODAY (), - ( _Months + 1 ) ) + 1 VAR _End = EOMONTH ( _Start, _Months ) VAR _Dates = CALCULATETABLE ( VALUES ( 'Shared Dates'[Date] ), 'Shared Dates'[Date] >= _Start && 'Shared Dates'[Date] <= _End ) RETURN CALCULATE ( DISTINCTCOUNT ( '1'[No.] ), _Dates )We can simplify it further using DATESINPERIOD like this.
!!INC RAG = VAR _Months = 3 VAR _Start = EOMONTH ( TODAY (), - (_Months + 1) ) + 1 RETURN CALCULATE ( DISTINCTCOUNT ( '1'[No.] ), DATESINPERIOD ( 'Shared Dates'[Date], _Start, 3, MONTH ) )
We can calculate the dates in the range then use that as a filter like this:
!!INC RAG =
VAR _Months = 3
VAR _Start = EOMONTH ( TODAY (), - ( _Months + 1 ) ) + 1
VAR _End = EOMONTH ( _Start, _Months )
VAR _Dates =
CALCULATETABLE (
VALUES ( 'Shared Dates'[Date] ),
'Shared Dates'[Date] >= _Start &&
'Shared Dates'[Date] <= _End
)
RETURN
CALCULATE ( DISTINCTCOUNT ( '1'[No.] ), _Dates )
We can simplify it further using DATESINPERIOD like this.
!!INC RAG =
VAR _Months = 3
VAR _Start = EOMONTH ( TODAY (), - (_Months + 1) ) + 1
RETURN
CALCULATE ( DISTINCTCOUNT ( '1'[No.] ), DATESINPERIOD ( 'Shared Dates'[Date], _Start, 3, MONTH ) )
Wow, thank you! The first solution actually does the same as my solution: it still takes the current month into accunt, however the second one worked like a dream 🙂