Improving Dax Measure to be more precise

[misiek5510]

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 on January, December and November. 

The part  today() - MONTH(today()) is supposed to do just that, however it seems to still include current month. 

Also, I'm currently using days as method of couting 3 months back, which is not best option as each month has slightly different numbers of days (28, 30 or 31), so when simply using 90 its always a little off. 

The part ( previousWeekDate - 90 ) looks 90 days back, however I would like to be more like previousmonth-3 or something to make sure its looking at the whole month. Any suggestions please ? 🙂 

!!INC RAG = 
VAR previousWeekDate =
    today() - MONTH(today())

RETURN
    CALCULATE( 
        DISTINCTCOUNT( '1'[No.] ),
        FILTER (
            all('Shared Dates'),
            'Shared Dates'[Date] <= previousWeekDate
                && 'Shared Dates'[Date] >= ( previousWeekDate - 90 )
        )
    ) /3

[jdbuchanan71 · Accepted solution]

@misiek5510 

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 ) )

[misiek5510]

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 🙂