Search code examples
ssaspowerbibusiness-intelligencedax

Calculated Column DAX Formula for Date Is Less Than Last Friday

I need to add a calculated column that returns 'Yes' if the row's date (datekey on this date table) is on or before last Friday, 'No' otherwise.

To start, I've got IF(DimDate[DateKey] < DATEVALUE("2017-10-27"), "Yes", "No") however, I obviously need the 2017-10-27 date to be dynamic.

Is there an application of the DATEDIFF function that will return the date of the last Friday?

Solution

  • You can make use of the WEEKDAY function to do the calculation.

    By working out the days to subtract in order to get the date of last Friday: (1 = Monday, 7 = Sunday)

    WEEKDAY  |  OFFSET
-------------------
   1     |    -3
   2     |    -4
   3     |    -5
   4     |    -6
   5     |    -7
   6     |    -1
   7     |    -2

    The following DAX measure can calculate last Friday:

    Last Friday = 
VAR WeekdayOfToday = WEEKDAY(TODAY(), 2)
RETURN
IF(
    WeekdayOfToday >= 6,
    TODAY() - (WeekdayOfToday - 5),
    TODAY() - (WeekdayOfToday + 2)
)

    Then you can use it for your calculated column:

    On or before Last Friday = IF(DimDate[DateKey] <= [Last Friday], "Yes", "No")

    (On or before should be <= by the way)

    Results:

    enter image description here