Subtract Date based Other column text Day of Week Name

[SaulM]

Hello everyone, 

I am banging my head over something so simple, but I must have a DAX writing block right now.

I want to subtract days from a date column based on another column' text value. If Day of Week Name column = "Monday", then subtract 5 days from Record date column. It can either be calculated column or measure. I have tried both, but cannot seem to get it.

"Day of Week Name" is text column and "End of Week Date" is a Date column.

I have tried the following DAX formulas:

Column = SWITCH(TRUE(),
'Table1'[Day of Week Name] = "Monday", DATE(YEAR('Table1'[End of Week Date] ), MONTH( 'Table1'[End of Week Date] ), DAY( 'Table1'[End of Week Date] ) -5),
'Table1'[Day of Week Name] = "Tuesday", DATE(YEAR('Table1'[End of Week Date] ), MONTH( 'Table1'[End of Week Date] ), DAY( 'Table1'[End of Week Date] ) -4),
'Table1'[Day of Week Name] = "Wednesday", DATE(YEAR('Table1'[End of Week Date] ), MONTH( 'Table1'[End of Week Date] ), DAY( 'Table1'[End of Week Date] ) -3),
'Table1'[Day of Week Name] = "Thursday", DATE(YEAR('Table1'[End of Week Date] ), MONTH( 'Table1'[End of Week Date] ), DAY( 'Table1'[End of Week Date] ) -2),
'Table1'[Day of Week Name] = "Friday", DATE(YEAR('Table1'[End of Week Date] ), MONTH( 'Table1'[End of Week Date] ), DAY( 'Table1'[End of Week Date] ) -1)
)

Gives me the error "An argument of function 'Date' has the wong data type or the result is too large or too small".

I am able to create a measure, but only for one record "Monday" and I don't want to creat 5 different measures. Seems like there has to be a better way.

Record Date = IF('Table1'[Day of Week Name] = "Monday",
DATE(YEAR('Table1'[End of Week Date] ),
MONTH('Table1'[End of Week Date] ) ,
DAY( 'Table1'[End of Week Date] ) -5
))

Thank you all in advanced! 

What I want is "Record Date" subtracting the number of days based on Mon, Tue, Wed.... to the "End of Week Date", Finding the exact date of the record based on the name of the "Day of Week" column as it will be within that week. "End of Week Date" are all Saturday dates (end of the week log).

Day of Week Name	End of Week Date (Saturday)	Record date
Monday	1/7/2023	1/2/2023		Monday = -5
Tuesday	1/7/2023	1/3/2023		Tuesday = -4
Wednesday	1/14/2023	1/11/2023		Wednesday = -3
Thrusday	1/14/2023	1/12/2023		Thursday = -2
Friday	1/14/2023	1/13/2023		Friday = -1
Monday	1/21/2023	1/16/2023
Wednesday	1/21/2023	1/18/2023
Thrusday	1/21/2023	1/19/2023
Wednesday	1/28/2023	1/25/2023
Friday	1/28/2023	1/27/2023
Tuesday	1/28/2023	1/24/2023
Monday	2/4/2023	1/30/2023
Wednesday	2/4/2023	2/1/2023
Friday	2/11/2023	2/10/2023
Tuesday	2/18/2023	2/14/2023

[johnt75]

Your code for the column should work. Check the underlying data to see what the max and min values are for 'Table1'[End of week date]. If you have blank values that could cause the problem.

[SaulM]

Thanks for the reply Johnt, I think you might be on to something. The data is from 01/07/23 to 06/3/2023, every Saturday from 2023 to 06/3/23. I did not think about this but, I am now wondering if the problem is because it's not continuous, data point dates are every 7 days? Suggestion?

[johnt75]

I don't think it will matter whether the dates are continuous or not, I think its more likely that a date is missing. Is it possible that blank rows at the end of the dataset are being included ?

Another option would be to shift the calculation to Power Query. You could create a custom column for the number of days to shift, based on the day name, and then create a new column using the Date.AddDays function, with the end of week date and number of days columns as parameters.

[SaulM]

No blanks or empty rows. I also check to make sure all Saturday dates are included and they are all included, every 7 days (Saturday) date is there. I am not strong in M, I will give Power Query a that a try. I am just still scratching my head as to why it will not work with the dax code.

[SaulM]

As an update: I when ahead and added the code in Power Query M with the following: 

each if [Day of Week Name] = "Sunday" then Date.AddDays([End of Week Date],-6) else if [Day of Week Name] = "Monday" then Date.AddDays([End of Week Date],-5) else if [Day of Week Name] = "Tuesday" then Date.AddDays([End of Week Date],-4) else if [Day of Week Name] = "Wednesday" then Date.AddDays([End of Week Date],-3) else if [Day of Week Name] = "Thursday" then Date.AddDays([End of Week Date],-5) else if [Day of Week Name] = "Friday" then Date.AddDays([End of Week Date],-1) else if [Day of Week Name] = "Saturday" then [End of Week Date] else "Blank")

Simple and it works. I am not sure why my DAX code does not work in Power BI, but this is my workaround. One table, no nulls or blanks in rows, just not continuous dates as I mentioned as it's all Saturday dates from Jan to Jun 2023.