Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Community,
I have the following calculation but I want to add an extra criterion to calculate this only if today's date is > 3. In other words, bring in the results for the following calculation only if today's day is Wednesday and onwards
Billable Util_CDL_WEEK = CALCULATE(SUM('cdl weekly'[Hours]),'TIME CATEGORY'[Time Category All]="Billable")/CALCULATE(SUM('cdl weekly'[Hours]),'TIME CATEGORY'[Time Category All]="Available")
I amend it to
Billable Util_CDL_WEEK = IF(TODAY()<3,"No Util",CALCULATE(SUM('cdl weekly'[Hours]),'TIME CATEGORY'[Time Category All]="Billable")/CALCULATE(SUM('cdl weekly'[Hours]),'TIME CATEGORY'[Time Category All]="Available"))
but it doesn't seem to work. It doesn't return any errors but it doesn't return the correct outcome either
Solved! Go to Solution.
you need to use WEEKDAY
https://msdn.microsoft.com/en-us/query-bi/dax/weekday-function-dax
WEEKDAY(TODAY(),2)<3
depending on whether the week starts on Saturday or Sunday you should pass a proper parameter instead of 2
Hi @harrinho,
Based on the syntax of WEEKDAY function, by default the day ranges from 1 (Sunday) to 7 (Saturday).
Syntax : WEEKDAY(<date>, <return_type>)
<return_type>:
A number that determines the return value:
Return type: 1, week begins on Sunday (1) and ends on Saturday (7). numbered 1 through 7.
Return type: 2, week begins on Monday (1) and ends on Sunday (7).
Return type: 3, week begins on Monday (0) and ends on Sunday (6).numbered 1 through 7.
For your scenario, your return type could be 1.
Best Regards,
Cherry
Hi @harrinho,
Have you solved the problem?
If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If you need additional help, please share some data sample and your expected output.
Best Regards,
Cherry
you need to use WEEKDAY
https://msdn.microsoft.com/en-us/query-bi/dax/weekday-function-dax
WEEKDAY(TODAY(),2)<3
depending on whether the week starts on Saturday or Sunday you should pass a proper parameter instead of 2
Thank you @Stachu. The week starts on Sunday, what would be the proper parameter?
Hi @harrinho,
Based on the syntax of WEEKDAY function, by default the day ranges from 1 (Sunday) to 7 (Saturday).
Syntax : WEEKDAY(<date>, <return_type>)
<return_type>:
A number that determines the return value:
Return type: 1, week begins on Sunday (1) and ends on Saturday (7). numbered 1 through 7.
Return type: 2, week begins on Monday (1) and ends on Sunday (7).
Return type: 3, week begins on Monday (0) and ends on Sunday (6).numbered 1 through 7.
For your scenario, your return type could be 1.
Best Regards,
Cherry
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |