Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to Solution.
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 🙂
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
104 | |
99 | |
98 | |
38 | |
37 |
User | Count |
---|---|
158 | |
120 | |
74 | |
72 | |
63 |