cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Patron

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?

1 ACCEPTED SOLUTION
Super User

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.

Proud to be a Super User!

Super User

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.

Proud to be a Super User!