cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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!

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors