Search code examples
datepowerbidaxpowerbi-desktopcalculated-columns

Power BI DAX how to create new column showing Last Friday of the month based on another date column

Is there a way to calculate the Last Friday of the month based on another date column.

I created a date column using

Date = CALENDAR(DATE(2015, 01, 01), DATE(2022, 12,31))

I know that I can use EOMONTH() in a new column to return the end of month for each date but is how would we return the Last Friday of that month?

Solution

  • This should work for you.

    Column = 
VAR eom = EOMONTH('Date'[Date],0)
VAR day = WEEKDAY('Date'[Date],1)

RETURN  IF(day == 6  &&  DAY('Date'[Date]) >= DAY(eom-7), TRUE() )

    UPDATE

    enter image description here

    Measure = 
VAR d =  DATESBETWEEN ('Date'[Date],  EOMONTH(SELECTEDVALUE('Table'[Column1]),0)-6, EOMONTH(SELECTEDVALUE('Table'[Column1]),0))
VAR f = FILTER(d, WEEKDAY( 'Date'[Date]) = 6)

RETURN 

MAXX(f, [Date])