Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
NewbieJono
Post Patron
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?

 

Capture.PNG

1 ACCEPTED SOLUTION
jgeddes
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.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

1 REPLY 1
jgeddes
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.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.