Forum Discussion

DJBAJG's avatar
DJBAJG
Icon for Helper IV rankHelper IV
3 years ago
Solved

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 ...
Need Help
  • TomMartens's avatar
    TomMartens
    3 years ago

    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.

    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:

    Regards,
    Tom



TomMartens's avatar
TomMartens
Icon for Super User rankSuper User
3 years ago

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.

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:

Regards,
Tom



  • DJBAJG's avatar
    DJBAJG
    Icon for Helper IV rankHelper IV
    3 years ago

    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")