Flags for this period vs previous period

DAX DAX Calculations
1

Hello,

I have a standard date table. I’d like to create 5 flag columns that indicate if the day, week, month, quarter or year is the current or previous period, as follows:

-Today Flag:** If the date is today’s date, the value should be “Today”. The row with the previous day’s value should be “Previous Day”. All other rows that aren’t flagged as “today” or “previous day” should be flagged “Other”.
-This Week Flag:** If today’s date falls in the current week, then the value for all rows in this week should be “this week”. The previous week’s values should be “Previous Week”. All others should be “Other”
-This Month Flag:** if today’s date falls in the current month, then all rows in that month should be “this month”. The previous month values should be “Previous Month”. All others should be “Other”.
-This Quarter Flag:** if today’s date falls in the current quarter, then all rows in that quarter should be “This Quarter”. The previous quarter values should be “Previous Quarter”. All others should be “Other”
-This Year Flag:** if today’s date falls in the current year, then all rows within that year should be “This Year”. The rows in the previous year should be “Previous Year”. All others should be “Other”.

As the data changes, then the flags should change dynamically such that the flags are always marking the current and previous period.

I wrote the dax for the Today Flag, as follows:

Today Flag = SWITCH(TRUE(),
‘Dates’[Date] = TODAY(), “Today”,
‘Dates’[Date] = TODAY()-1, “Previous Day”, “Other”)

I was also able to successfully write the DAX for the This Week Flag:

This Week Flag = SWITCH(TRUE(),
WEEKNUM ( Dates[Date] ) = WEEKNUM ( TODAY () ) && YEAR(Dates[Date]) = YEAR(TODAY()), “This Week”,
WEEKNUM(Dates[Date]) = WEEKNUM(TODAY()) - 1 && YEAR(Dates[Date]) = YEAR(TODAY()), “Previous Week”, “Other”)

I attempted to create the “This Month Flag” with the following DAX:

This Month Flag = SWITCH(TRUE(),
MONTH (Dates[Date]) = month ( TODAY () ) && YEAR(Dates[Date]) = YEAR(TODAY()), “This Month”,
MONTH(Dates[Date]) = MONTH(NOW())-1 && YEAR(Dates[Date]) = YEAR(TODAY()), “Previous Month”, “Other”)

The result is correct for “This Month”, however it does not correctly flag “Previous Month”. All other results are “Other”.

However, I’m not sure how to do the remaining flags for month, quarter and year.

If you know how to create these flag columns using DAX, please share!

Thanks,
Pete

2

@pete.langlois,

If I can make a suggestion (two, actually) to go down another track:
a) this task is much better suited to Power Query than DAX
b) if you can, ditch your standard data table and substitute @Melissa’s awesome Extended Date Table.

The daily, weekly, monthly and yearly offsets in this date table will make this calculation a breeze.

Here’s the link to the date table code:

The date table video:

https://portal.enterprisedna.co/courses/303649/lectures/23833092

And the video on using the offsets:

Should give you everything you need, but if you still have questions just give a shout.

I hope this is helpful.

– Brian

1 Like
3

Thanks, @BrianJ! My client’s SQL guys went ahead and built the columns for me on the back end…however now I’m simply curious from a learning perspective on how to write this code in DAX on the front end. I appreciate the reference - I’ll check out the videos and see what they say. I’d still love to figure out how to do the month, quarter and year flags with DAX.

4

Hi @pete.langlois.

I totally agree with @BrianJ, @Melissa’s extended date table in Power Query is the way to go.

Just for an exercise, I made some mild updates to the base [Dates] table in DAX, adding [YearQuarter], [YearMonth], and [YearWeek] columns. I can then use these new columns in simplified DAX to generate your flags.

NOTE: This solution falls down on quarter, month, and week flags at the year boundaries, but could be easily fixed.

Dates = 
ADDCOLUMNS(
    CALENDAR( DATE( 2020, 1, 1 ), DATE( 2021, 12, 31 )
    ),
    "Year", YEAR( [Date] ),
    "Quarter", QUARTER( [Date] ),
    "Month", MONTH( [Date] ),
    "Week", WEEKNUM( [Date] ),
    "Day", DAY( [Date] ),
    "YearQuarter", YEAR( [Date] ) * 10 + QUARTER( [Date] ),
    "YearMonth", YEAR( [Date] ) * 100 + MONTH( [Date] ),
    "YearWeek", YEAR( [Date] ) * 100 + WEEKNUM( [Date] )
)

Today Flag = 
SWITCH( TRUE(),
    Dates[Date] = TODAY(), "Today",
    Dates[Date] = TODAY() - 1, "Previous Day",
    "Other"
    )
	
Week Flag = 
VAR _CurrentYearWeek = 
    CALCULATE( 
        MAX( Dates[YearWeek] ), 
        FILTER( Dates, Dates[Today Flag] = "Today" ) )
RETURN
SWITCH( TRUE(),
    Dates[YearWeek] = _CurrentYearWeek, "This Week",
    Dates[YearWeek] = _CurrentYearWeek - 1, "Previous Week",
    "Other"
    )

Month Flag = 
VAR _CurrentYearMonth = 
    CALCULATE( 
        MAX( Dates[YearMonth] ), 
        FILTER( Dates, Dates[Today Flag] = "Today" ) )
RETURN
SWITCH( TRUE(),
    Dates[YearMonth] = _CurrentYearMonth, "This Month",
    Dates[YearMonth] = _CurrentYearMonth - 1, "Previous Month",
    "Other"
    )

Quarter Flag = 
VAR _CurrentYearQuarter = 
    CALCULATE( 
        MAX( Dates[YearQuarter] ), 
        FILTER( Dates, Dates[Today Flag] = "Today" ) )
RETURN
SWITCH( TRUE(),
    Dates[YearQuarter] = _CurrentYearQuarter, "This Quarter",
    Dates[YearQuarter] = _CurrentYearQuarter - 1, "Previous Quarter",
    "Other"
    )

Year Flag = 
VAR _CurrentYear = 
    CALCULATE( 
        MAX( Dates[Year] ), 
        FILTER( Dates, Dates[Today Flag] = "Today" ) )
RETURN
SWITCH( TRUE(),
    Dates[Year] = _CurrentYear, "This Year",
    Dates[Year] = _CurrentYear - 1, "Previous Year",
    "Other"
    )

As always, ensure your [Dates] table is marked as a date table.

There are many resources online for generating date tables in DAX. An extensive one is available as a template from SQLBI:

Hope this helps.
Greg
eDNA Forum - Date table with Flags in DAX.pbix (36.9 KB)

3 Likes