cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper III

## 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 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``````

1 ACCEPTED SOLUTION
Super User

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

2 REPLIES 2
Super User

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

Helper III

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 🙂

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors