Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
DJBAJG
Helper III
Helper III

Creating a Prior Month Indicator Column - that works year over year

"that works year over year" - That's the trick

 

I've got one that works if the year is the same ie. I can get June 2022 if it's currently July 2022 but once it changes to January 2023 I can't get December 2022. I need a column on my date table and not a measure as I need to return month names for my visuals. I'm pretty sure PREVIOUSMONTH and DATEADD aren't going to work here but I'm happy if I’m wrong. I need it to be variable as well, meaning I can easily change to 2 month prior as well, ideally by simply changing a mathematical component.

 

Thanks!

 

Here's what I currently have.

 

Prior Month Column = IF(YEAR(TODAY()) = YEAR(Dates[Date]) && (MONTH(TODAY()) - 1) = MONTH(Dates[Date]), "Yes", "No")

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

Hey @DJBAJG ,

this DAX statement shows how to create a running month index column:

 

calendar = 
var dateStart = DATE( 2022 , 1 , 1 )
 var dateEnd = DATE( 2023 , 12, 31 )
 return

 ADDCOLUMNS(
    CALENDAR(
        dateStart
        , dateEnd
    )
    , "runningMonthIndexCalendar"
        , var YearsNoOf = YEAR( [Date] ) - YEAR( dateStart )
        var MonthNoOf = MONTH( [Date] )
        return
        YearsNoOf * 12 + MonthNoOf
 )
        

 

Here is a screenshot that shows that the index is incremented from December 2022 to January 2023.

TomMartens_0-1672783166980.png

Using this index will help to get the previous month's name or will help to shift dates by months for more sophisticated date-related calculations.

This adds a column to the calendar table that holds the prev month name:

, "Year Month (prev)"
            , var eomprev = EOMONTH( [Date] , -1 )
            return
            FORMAT( eomprev , "YYYY MMM" )

Another screenshot:
image.png
Regards,
Tom





Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

2 REPLIES 2
TomMartens
Super User
Super User

Hey @DJBAJG ,

this DAX statement shows how to create a running month index column:

 

calendar = 
var dateStart = DATE( 2022 , 1 , 1 )
 var dateEnd = DATE( 2023 , 12, 31 )
 return

 ADDCOLUMNS(
    CALENDAR(
        dateStart
        , dateEnd
    )
    , "runningMonthIndexCalendar"
        , var YearsNoOf = YEAR( [Date] ) - YEAR( dateStart )
        var MonthNoOf = MONTH( [Date] )
        return
        YearsNoOf * 12 + MonthNoOf
 )
        

 

Here is a screenshot that shows that the index is incremented from December 2022 to January 2023.

TomMartens_0-1672783166980.png

Using this index will help to get the previous month's name or will help to shift dates by months for more sophisticated date-related calculations.

This adds a column to the calendar table that holds the prev month name:

, "Year Month (prev)"
            , var eomprev = EOMONTH( [Date] , -1 )
            return
            FORMAT( eomprev , "YYYY MMM" )

Another screenshot:
image.png
Regards,
Tom





Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi Tom

 

How would I go about incorporating this DAX into an existing calendar? I believe this builds a new calendar table from scratch with hard coded start and end dates. I'll need dynamic date capabilities as new data is added monthly.

 

Thanks.

 

 

Played around and got it to work.

 

Here's how to add it to an existing calendar table:

 

Running Month Index Column =
VAR YearsNoOf = YEAR(Dates[Date]) - YEAR(MIN(Dates[Date]))
VAR MonthNoOf = MONTH(Dates[Date])
RETURN
YearsNoOf * 12 + MonthNoOf
 
Here's how to add a column indicating if the month is the prior month. This is what I needed for my application:
 
Prior Month Column = IF(Dates[Running Month Index Column] = MAX(Dates[Running Month Index Column]) -1
, "Yes", "No")

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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