Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JemmaD
Resolver II
Resolver II

Creating an 'Historical month' flag in Dax calculated column

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?

1 ACCEPTED SOLUTION
JemmaD
Resolver II
Resolver II

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/

 

View solution in original post

6 REPLIES 6
JemmaD
Resolver II
Resolver II

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/

 

Greg_Deckler
Super User
Super User

@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'.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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