Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I want to create a flag which shows 1 for historical months - any month before the current month so if we are in July 2022, any date before 1st July 2022 would be a 1 and anything after 1st July 2022 would be a 0.
I tried the below which I found online but it's not working in the slightest:
Historical Month = IF(YEAR('Dates'[Date]) <= YEAR(TODAY()) && MONTH('Dates'[Date]) < MONTH(TODAY()),1,0)
Can anyone help?
Solved! Go to Solution.
I found a solution!
I created a month/year index using the Dax below and converting to whole number
Month Index = CONCATENATE('Dates'[Year],RIGHT("0"&'Dates'[Month Number],2))
I then created my history flag:
VAR ThisMonthID =
LOOKUPVALUE('Dates'[Month Index], 'Dates'[Date], TODAY())
RETURN 'Dates'[Month Index] < ThisMonthID
I found this solution here
https://exceleratorbi.com.au/dynamically-show-completed-months-in-power-bi/
I found a solution!
I created a month/year index using the Dax below and converting to whole number
Month Index = CONCATENATE('Dates'[Year],RIGHT("0"&'Dates'[Month Number],2))
I then created my history flag:
VAR ThisMonthID =
LOOKUPVALUE('Dates'[Month Index], 'Dates'[Date], TODAY())
RETURN 'Dates'[Month Index] < ThisMonthID
I found this solution here
https://exceleratorbi.com.au/dynamically-show-completed-months-in-power-bi/
@JemmaD That formula should work as a DAX calculated column. Are you trying to create a measure? If so then you will need to put an aggregation function like MAX or MIN around column references. If you are trying to use it in your query, then that is an entirely different language. Only other thing is if your date table is called something other than 'Dates'.
@Greg_Deckler yes it is a calculated column, and it's not working at all. It has 1s and 0s against a variety of dates both historical and future.
@JemmaD I see the problem, it is the <= should be an = for YEAR
@Greg_Deckler nope still not working, what it's doing is showing 0 for all dates prior to Jan-22, 1 for Jan-22 to Jun-22 and 0 for Aug-22 onwards.
Historical Month = IF(YEAR('Dates'[Date]) = YEAR(TODAY()) && MONTH('Dates'[Date]) < MONTH(TODAY()),1,0)
If I change the Year to < it displays a 1 for all months prior to current month in other years, and a 0 for all current and future months in other years. It's as if it's looking at month and year entirely separately.
I simply want it to ask, is the date before the current month? If yes, then 1, else 0.
So 1 would be against all dates up to and including June 2022 as we are now in July 2022. My future dates and July 2022 should be a 0.
@JemmaD You just had to switch your 1 and 0 around and change MONTH to >=. Or, you could have used < for YEAR.
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |