Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi!
I want to flag down dates that fall in the last 13 months. But there's an exception.
If today is not the last date of the month then I need to go to the previous month and count 13 months backwards.
I understand I need an upper limit and a lower limit.
Upper limit = EOMONTH(NOW(),-1)
This is as far as I got. I need to count 13 months from the upper limit. Can someone please help me.
Solved! Go to Solution.
I think you want something like this:
VAR _Today = TODAY ()
VAR _Today_Is_EoM = EOMONTH ( _Today, 0 ) = _Today
VAR _Upper = IF ( _Today_Is_EoM, EOMONTH ( _Today, -1 ), _Today )
VAR _Lower = EOMONTH ( _Upper, -13 )
VAR _Range_ =
FILTER (
DateTable,
DateTable[Date] >= _Lower &&
DateTable[Date] <= _Upper
)
[...]
I think you want something like this:
VAR _Today = TODAY ()
VAR _Today_Is_EoM = EOMONTH ( _Today, 0 ) = _Today
VAR _Upper = IF ( _Today_Is_EoM, EOMONTH ( _Today, -1 ), _Today )
VAR _Lower = EOMONTH ( _Upper, -13 )
VAR _Range_ =
FILTER (
DateTable,
DateTable[Date] >= _Lower &&
DateTable[Date] <= _Upper
)
[...]
Thank you so much 😊
This worked perfect I tweaked the if statement part
VAR _Today = TODAY ()
VAR _Today_Is_EoM = EOMONTH ( _Today, 0 ) = _Today
VAR _Upper = IF ( _Today_Is_EoM, _Today, EOMONTH ( _Today, -1 ), _Today )
VAR _Lower = EOMONTH ( _Upper, -13 )
VAR _Range_ =
FILTER (
DateTable,
DateTable[Date] > _Lower &&
DateTable[Date] <= _Upper
)
It was counting Oct 31 of 2022 so I took the = off the evaluation part for lower piece
User | Count |
---|---|
98 | |
91 | |
84 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |