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 all,
I am trying to edit an incorrect DAX code which is pulling out values as per this table:
The expected values as as per the Expected Excel column.
Employee Name | AUD Total Daily Earnings | CalculatedPay | Calculation Date | Applicable Payroll Date | Calculated Pay (N) | Expected (Excel Formula) |
Arsher B | $327.81 | $2,031.34 | 1/09/2013 0:00 | 1/09/2013 0:00 | 2031.34 | 2031.34 |
Arsher B | $178.60 | $0.00 | 4/09/2013 0:00 | 15/09/2013 0:00 | 1959.18 | 0 |
Arsher B | $185.73 | $0.00 | 5/09/2013 0:00 | 15/09/2013 0:00 | 1959.18 | 0 |
Arsher B | $162.02 | $0.00 | 6/09/2013 0:00 | 15/09/2013 0:00 | 1959.18 | 0 |
Arsher B | $228.94 | $0.00 | 7/09/2013 0:00 | 15/09/2013 0:00 | 1959.18 | 0 |
Arsher B | $292.16 | $0.00 | 8/09/2013 0:00 | 15/09/2013 0:00 | 1959.18 | 0 |
Arsher B | $151.95 | $0.00 | 11/09/2013 0:00 | 15/09/2013 0:00 | 1959.18 | 0 |
Arsher B | $151.96 | $0.00 | 12/09/2013 0:00 | 15/09/2013 0:00 | 1959.18 | 0 |
Arsher B | $151.96 | $0.00 | 13/09/2013 0:00 | 15/09/2013 0:00 | 1959.18 | 0 |
Arsher B | $189.94 | $0.00 | 14/09/2013 0:00 | 15/09/2013 0:00 | 1959.18 | 0 |
Arsher B | $265.92 | $1,959.18 | 15/09/2013 0:00 | 15/09/2013 0:00 | 1959.18 | 1959.18 |
Arsher B | $141.82 | $0.00 | 18/09/2013 0:00 | 29/09/2013 0:00 | 2063.78 | 0 |
Arsher B | $166.15 | $0.00 | 19/09/2013 0:00 | 29/09/2013 0:00 | 2063.78 | 0 |
Arsher B | $271.50 | $0.00 | 20/09/2013 0:00 | 29/09/2013 0:00 | 2063.78 | 0 |
Arsher B | $192.47 | $0.00 | 21/09/2013 0:00 | 29/09/2013 0:00 | 2063.78 | 0 |
Arsher B | $269.46 | $0.00 | 22/09/2013 0:00 | 29/09/2013 0:00 | 2063.78 | 0 |
Arsher B | $151.95 | $0.00 | 25/09/2013 0:00 | 29/09/2013 0:00 | 2063.78 | 0 |
Arsher B | $151.96 | $0.00 | 26/09/2013 0:00 | 29/09/2013 0:00 | 2063.78 | 0 |
Arsher B | $187.24 | $0.00 | 27/09/2013 0:00 | 29/09/2013 0:00 | 2063.78 | 0 |
Arsher B | $221.65 | $0.00 | 28/09/2013 0:00 | 29/09/2013 0:00 | 2063.78 | 0 |
Arsher B | $309.58 | $2,063.78 | 29/09/2013 0:00 | 29/09/2013 0:00 | 2063.78 | 2063.78 |
Daniel Gelat | $151.96 | $0.00 | 30/09/2013 0:00 | 13/10/2013 0:00 | 2167.23 | 0 |
Daniel Gelat | $171.93 | $0.00 | 1/10/2013 0:00 | 13/10/2013 0:00 | 2167.23 | 0 |
Daniel Gelat | $151.95 | $0.00 | 2/10/2013 0:00 | 13/10/2013 0:00 | 2167.23 | 0 |
Daniel Gelat | $289.32 | $0.00 | 5/10/2013 0:00 | 13/10/2013 0:00 | 2167.23 | 0 |
Daniel Gelat | $333.89 | $0.00 | 6/10/2013 0:00 | 13/10/2013 0:00 | 2167.23 | 0 |
Daniel Gelat | $405.20 | $0.00 | 7/10/2013 0:00 | 13/10/2013 0:00 | 2167.23 | 0 |
Daniel Gelat | $151.96 | $0.00 | 8/10/2013 0:00 | 13/10/2013 0:00 | 2167.23 | 0 |
Daniel Gelat | $141.82 | $0.00 | 9/10/2013 0:00 | 13/10/2013 0:00 | 2167.23 | 0 |
Daniel Gelat | $187.85 | $0.00 | 10/10/2013 0:00 | 13/10/2013 0:00 | 2167.23 | 0 |
Daniel Gelat | $181.35 | $0.00 | 11/10/2013 0:00 | 13/10/2013 0:00 | 2167.23 | 2167.23 |
Daniel Gelat | $169.19 | $0.00 | 14/10/2013 0:00 | 27/10/2013 0:00 | 2036.55 | 0 |
Daniel Gelat | $184.08 | $0.00 | 15/10/2013 0:00 | 27/10/2013 0:00 | 2036.55 | 0 |
Daniel Gelat | $173.75 | $0.00 | 18/10/2013 0:00 | 27/10/2013 0:00 | 2036.55 | 0 |
Daniel Gelat | $247.18 | $0.00 | 19/10/2013 0:00 | 27/10/2013 0:00 | 2036.55 | 0 |
Daniel Gelat | $285.67 | $0.00 | 20/10/2013 0:00 | 27/10/2013 0:00 | 2036.55 | 0 |
Daniel Gelat | $151.96 | $0.00 | 21/10/2013 0:00 | 27/10/2013 0:00 | 2036.55 | 0 |
Daniel Gelat | $151.96 | $0.00 | 22/10/2013 0:00 | 27/10/2013 0:00 | 2036.55 | 0 |
Daniel Gelat | $151.96 | $0.00 | 23/10/2013 0:00 | 27/10/2013 0:00 | 2036.55 | 0 |
Daniel Gelat | $254.88 | $0.00 | 26/10/2013 0:00 | 27/10/2013 0:00 | 2036.55 | 0 |
Daniel Gelat | $265.92 | $2,036.55 | 27/10/2013 0:00 | 27/10/2013 0:00 | 2036.55 | 2036.55 |
Daniel Gelat | $202.35 | $0.00 | 30/10/2013 0:00 | 10/11/2013 0:00 | 1928.3 | 0 |
Daniel Gelat | $190.45 | $0.00 | 31/10/2013 0:00 | 10/11/2013 0:00 | 1928.3 | 1928.3 |
The DAX that calculates the CalculatedPay column is as below. However, since some times the Calculation Date is skipped (not equal) to the Applicable Payroll Date, it outputs 0 which is not desired.
CalculatedPay = IF
(
[Calculation Date] < [Applicable Payroll Date],
--then--
0,
--else--
VAR payrollId = [Payroll Day ID]
VAR empID = [Employee_ID]
RETURN CALCULATE(SUM('Ordinary Hours'[AUD Total Daily Earnings]),FILTER('Ordinary Hours',[Payroll Day ID]=payrollId && [Employee_ID]=empID))
)
I then tried to do a group by sum as below however, it repeats the column values and when I display in the viz, the aggregate sum of that column is incorrect
Calculated Pay (N) =
CALCULATE (
SUM ( 'Ordinary Hours'[AUD Total Daily Earnings] ),
ALLEXCEPT (
'Ordinary Hours',
'Ordinary Hours'[Employee Name],
'Ordinary Hours'[Applicable Payroll Date].[Date]
)
)
The excel formula that works is:
=IF(AND(E2>=D2,F2<>F3),F2,0)
Of course excel can refer to cells in above and below rows but this is the DAX part (I am new to this) I am trying to figure out.
@Greg_Deckler suggested me to go down the index path here however, I am to new with Power BI to do it correctly.
Thank you so much!
Please help me with the correct code for the CalculatedPay or Calculated Pay (N) column.
Many Many Thanks!
try like a measure
Measure =
var _nextRow = CALCLATE(min('Ordinary Hours'[Calculation Date]), ALLEXCEPT('Ordinary Hours', 'Ordinary Hours'[Employee Name], 'Ordinary Hours'[Payroll Day ID]) )
RETURN
IF (SELECTEDVALUE('Ordinary Hours'[Applicable Payroll Date]) >= SELECTEDVALUE('Ordinary Hours'[Calculation Date]),
0,
CALCULATE(SUM('Ordinary Hours'[AUD Total Daily Earnings]), ALLEXCEPT('Ordinary Hours', 'Ordinary Hours'[Employee Name], 'Ordinary Hours'[Payroll Day ID]), 'Ordinary Hours'[Calculation Date]=_nextRow)
)
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 |
---|---|
49 | |
26 | |
21 | |
16 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |