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.
I have the following measure to calculate overtime hours as the difference between actual hours worked [Hours] and 'regular' hours [OT Base2]:
OT Hoursv2 = IF(CALCULATE([Hours], all(refDatesDAY[Date])) - calculate([OT Base2], all(refDatesDAY[Date]))<0,0, CALCULATE([Hours], all(refDatesDAY[Date])) - calculate([OT Base2], all(refDatesDAY[Date])) )
The RefDatesDay table is daily, so the measure is setup to give monthly totals (intended, daily calculations are not accurate). The totals my month are calculating correctly, but the total for the year is taking the difference of the two figures rather than the sum of the months:
I've tried wrapping in a SUMX, but no luck.
What DAX should I use to get the total above to be the sum of the months (4.5+2.5+6+20=33) rather than 2.5?
Thanks
Hi @connork,
Have you checked this article? Dealing with Measure Totals
Besides, if possible, please post some dummy data in source table.
Regards,
Yuliana Gu
Ok - here's the data, the measures and then a videw of the matrix with related columns:
DATA
MEASURES
OT Hoursv2 = IF(CALCULATE([Hours], all(refDatesDAY[Date])) - calculate([OT Base2], all(refDatesDAY[Date]))<0,0, CALCULATE([Hours], all(refDatesDAY[Date])) - calculate([OT Base2], all(refDatesDAY[Date])) )
OT Base2 = calculate(sum(refDatesDAY[OTBaseHours]), filter(refDatesDAY, refDatesDAY[Date]<=[MeasTerm] &&refDatesDAY[Date]>=[MeasHire]))
Hours = sum(dataTime[EMP_HOURS])
MeasHire = maX(refEmployee[Hire])
MeasTerm = max(refEmployee[Term])
REPORT VIEW
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 |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |