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.
Hello, I need help with the following measure.
I have a measure that calculates "Allowed $" based on hours worked. We updated the "Allowed $" for the new year. I need to edit the measure to calculate the old Allowed $ for dates prior to 12/31/2022 and calculate the new allowed amount for dates on/after 1/1/2023.
Please see my current measure. Thank you.
Solved! Go to Solution.
Please try this combined measure.
NewMeasures =
VAR afterdate =
DATE ( 2023, 1, 1 )
VAR after =
CALCULATE (
SUMX (
ADDCOLUMNS (
SUMMARIZE (
Timesheet,
Timesheet[Employee ID],
Timesheet[Date],
Timesheet[Holiday],
Timesheet[Day of Week]
),
"DailyHrs", CALCULATE ( SUM ( Timesheet[Number (unit)] ) )
),
SWITCH (
TRUE,
[DailyHrs] >= 4
&& [DailyHrs] < 8
&& Timesheet[Holiday] = "Yes", 18,
[DailyHrs] >= 8
&& Timesheet[Holiday] = "Yes", 45,
[DailyHrs] >= 10
&& 'Timesheet'[Day of Week] <= 4, 45,
[DailyHrs] >= 4
&& [DailyHrs] < 8
&& 'Timesheet'[Day of Week] >= 5, 18,
[DailyHrs] >= 8
&& 'Timesheet'[Day of Week] >= 5, 45,
0
)
),
Timesheet[Date] >= afterdate
)
VAR before =
CALCULATE (
SUMX (
ADDCOLUMNS (
SUMMARIZE (
Timesheet,
Timesheet[Employee ID],
Timesheet[Date],
Timesheet[Holiday],
Timesheet[Day of Week]
),
"DailyHrs", CALCULATE ( SUM ( Timesheet[Number (unit)] ) )
),
SWITCH (
TRUE,
[DailyHrs] >= 4
&& [DailyHrs] < 8
&& Timesheet[Holiday] = "Yes", 12,
[DailyHrs] >= 8
&& Timesheet[Holiday] = "Yes", 32,
[DailyHrs] >= 10
&& 'Timesheet'[Day of Week] <= 4, 20,
[DailyHrs] >= 4
&& [DailyHrs] < 8
&& 'Timesheet'[Day of Week] >= 5, 12,
[DailyHrs] >= 8
&& 'Timesheet'[Day of Week] >= 5, 32,
0
)
),
Timesheet[Date] < afterdate
)
RETURN
after + before
Pat
Please try this combined measure.
NewMeasures =
VAR afterdate =
DATE ( 2023, 1, 1 )
VAR after =
CALCULATE (
SUMX (
ADDCOLUMNS (
SUMMARIZE (
Timesheet,
Timesheet[Employee ID],
Timesheet[Date],
Timesheet[Holiday],
Timesheet[Day of Week]
),
"DailyHrs", CALCULATE ( SUM ( Timesheet[Number (unit)] ) )
),
SWITCH (
TRUE,
[DailyHrs] >= 4
&& [DailyHrs] < 8
&& Timesheet[Holiday] = "Yes", 18,
[DailyHrs] >= 8
&& Timesheet[Holiday] = "Yes", 45,
[DailyHrs] >= 10
&& 'Timesheet'[Day of Week] <= 4, 45,
[DailyHrs] >= 4
&& [DailyHrs] < 8
&& 'Timesheet'[Day of Week] >= 5, 18,
[DailyHrs] >= 8
&& 'Timesheet'[Day of Week] >= 5, 45,
0
)
),
Timesheet[Date] >= afterdate
)
VAR before =
CALCULATE (
SUMX (
ADDCOLUMNS (
SUMMARIZE (
Timesheet,
Timesheet[Employee ID],
Timesheet[Date],
Timesheet[Holiday],
Timesheet[Day of Week]
),
"DailyHrs", CALCULATE ( SUM ( Timesheet[Number (unit)] ) )
),
SWITCH (
TRUE,
[DailyHrs] >= 4
&& [DailyHrs] < 8
&& Timesheet[Holiday] = "Yes", 12,
[DailyHrs] >= 8
&& Timesheet[Holiday] = "Yes", 32,
[DailyHrs] >= 10
&& 'Timesheet'[Day of Week] <= 4, 20,
[DailyHrs] >= 4
&& [DailyHrs] < 8
&& 'Timesheet'[Day of Week] >= 5, 12,
[DailyHrs] >= 8
&& 'Timesheet'[Day of Week] >= 5, 32,
0
)
),
Timesheet[Date] < afterdate
)
RETURN
after + before
Pat
Hi @Coryanthony ,
According to your requirements description, you want to achieve the purpose of merging into one measure according to the logic of [date BEFORE/After 1/1/2023] according to the two measures provided in the reply above, right?
If this is the case, you might consider trying this measure to logically combine two measures using the If() function.
Allowed OT $ =
If(Max(Timesheet[Date])<Date(2023,1,1),
SumX(SUMMARIZE(
Timesheet,
Timesheet[Employee ID],Timesheet[Date],Timesheet[Holiday],Timesheet[Day of Week],
"DailyHrs",sum(Timesheet[Number (unit)])),
SWITCH(TRUE,
[DailyHrs]>=4 && [DailyHrs]<8 && Timesheet[Holiday] = "Yes" ,12,
[DailyHrs]>=8 && Timesheet[Holiday] = "Yes" ,32,
[DailyHrs]>=10 &&'Timesheet'[Day of Week]<=4,20,
[DailyHrs]>=4 && [DailyHrs]<8 && 'Timesheet'[Day of Week]>=5,12,
[DailyHrs]>=8 && 'Timesheet'[Day of Week]>=5,32,
0
))
,
SumX(SUMMARIZE(
Timesheet,
Timesheet[Employee ID],Timesheet[Date],Timesheet[Holiday],Timesheet[Day of Week],
"DailyHrs",sum(Timesheet[Number (unit)])),
SWITCH(TRUE,
[DailyHrs]>=4 && [DailyHrs]<8 && Timesheet[Holiday] = "Yes" ,18,
[DailyHrs]>=8 && Timesheet[Holiday] = "Yes" ,45,
[DailyHrs]>=10 &&'Timesheet'[Day of Week]<=4,45,
[DailyHrs]>=4 && [DailyHrs]<8 && 'Timesheet'[Day of Week]>=5,18,
[DailyHrs]>=8 && 'Timesheet'[Day of Week]>=5,45,
0
))
)
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Share some data (in a format that can be pasted in an MS Excel file), explain the question and show the expected result.
Switching to the ADDCOLUMNS(SUMMARIZE pattern should help, but the SWITCH evaluation on every row may still keep things slow.
Allowed OT $ =
SUMX (
ADDCOLUMNS (
SUMMARIZE (
Timesheet,
Timesheet[Employee ID],
Timesheet[Date],
Timesheet[Holiday],
Timesheet[Day of Week]
),
"DailyHrs", CALCULATE ( SUM ( Timesheet[Number (unit)] ) )
),
SWITCH (
TRUE,
[DailyHrs] >= 4
&& [DailyHrs] < 8
&& Timesheet[Holiday] = "Yes", 12,
[DailyHrs] >= 8
&& Timesheet[Holiday] = "Yes", 32,
[DailyHrs] >= 10
&& 'Timesheet'[Day of Week] <= 4, 20,
[DailyHrs] >= 4
&& [DailyHrs] < 8
&& 'Timesheet'[Day of Week] >= 5, 12,
[DailyHrs] >= 8
&& 'Timesheet'[Day of Week] >= 5, 32,
0
)
)
Pat
Hi @Ashish_Mathur and @ppm1
To simplify: I need below measure to reflect on date BEFORE 1/1/2023.
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |