Help Please Incorrect DAX code

[harshad_barge]

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!

[Anonymous]

I can't understand why you people always want to write complex DAX, completely unmaintainable and hardly comprehensible when things are so dead simple in Power Query... Mate, do yourself a favor and please do this calculation in POwer Query. You'll thank me later.

Best
D

[harshad_barge]

I would if I could. This is a calculated table. I am trying to write DAX on a calculated table. Hence cannot do this in Power query.

[Anonymous]

Calculate the table in PQ as well.

Best
D

[az38]

@harshad_barge 

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) 
)