Forum Discussion
DAX calculation
Hello, i have a tricky situation, im not sure if its possible
table 1 (input table) shows the data structure available, table 2 shows the output I need. What is happening is every Monday, I need the results of 1-2 days to be combined and moved into the 0-1 days. This only happens on a Monday.
not sure if this is possible?
You could likely use a condition that uses the WEEKDAY function in your measure.
Here is an example measure that returns 0 for Saturday and Sunday, the sum for Saturday, Sunday and Monday on Mondays and the normal sum for all other days.0-1 Days = SWITCH( TRUE(), //evaluate statements that are true WEEKDAY(SELECTEDVALUE(Sheet1[Current Date]), 2) > 5, //determines if the current day of the week is Saturday or Sunday (Monday = 1) 0, //returns 0 if Saturday or Sunday WEEKDAY(selectedvalue(Sheet1[Current Date]), 2) = 1, //determines if the current day of the week is Monday (Monday = 1) CALCULATE(SUM(Sheet1[Number of hours]), DATESINPERIOD(Sheet1[Current Date], SELECTEDVALUE(Sheet1[Current Date]), -3, DAY)), //returns the sum for Saturday, Sunday and Monday if the day = 1 SUM(Sheet1[Number of hours]) //for all other days of the week just return the sum )Hope this points you in the right direction.
1 Reply
You could likely use a condition that uses the WEEKDAY function in your measure.
Here is an example measure that returns 0 for Saturday and Sunday, the sum for Saturday, Sunday and Monday on Mondays and the normal sum for all other days.0-1 Days = SWITCH( TRUE(), //evaluate statements that are true WEEKDAY(SELECTEDVALUE(Sheet1[Current Date]), 2) > 5, //determines if the current day of the week is Saturday or Sunday (Monday = 1) 0, //returns 0 if Saturday or Sunday WEEKDAY(selectedvalue(Sheet1[Current Date]), 2) = 1, //determines if the current day of the week is Monday (Monday = 1) CALCULATE(SUM(Sheet1[Number of hours]), DATESINPERIOD(Sheet1[Current Date], SELECTEDVALUE(Sheet1[Current Date]), -3, DAY)), //returns the sum for Saturday, Sunday and Monday if the day = 1 SUM(Sheet1[Number of hours]) //for all other days of the week just return the sum )Hope this points you in the right direction.