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

Question

"that works year over year" - That's the trick&nbsp;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.&nbsp;Thanks!&nbsp;Here's what I currently have.&nbsp;Prior Month Column = IF(YEAR(TODAY()) = YEAR(Dates[Date]) &amp;&amp; (MONTH(TODAY()) - 1) = MONTH(Dates[Date]), "Yes", "No")

tommartens · Accepted Answer

Hey&nbsp;DJBAJG&nbsp;,this DAX statement shows how to create a running month index column:
&nbsp;
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
 )
        
&nbsp;
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

Forum Discussion

Related content

Create columns group

Creating Colored Indicators

Creating a date column

creating of measure to do column comparison and creating a new column with indicators

Creating a custom column