Help Please Incorrect DAX code

harshad_barge — Fri, 28 Feb 2020 06:44:26 GMT

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!

Re: Help Please Incorrect DAX code

az38 — Fri, 28 Feb 2020 07:16:17 GMT

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

Re: Help Please Incorrect DAX code

Anonymous — Fri, 28 Feb 2020 10:18:47 GMT

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

Re: Help Please Incorrect DAX code

harshad_barge — Fri, 28 Feb 2020 10:22:12 GMT

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.

Re: Help Please Incorrect DAX code

Anonymous — Fri, 28 Feb 2020 10:35:40 GMT

Calculate the table in PQ as well.

Best
D

topic Re: Help Please Incorrect DAX code in DAX Commands and Tips

Help Please Incorrect DAX code

Re: Help Please Incorrect DAX code

Re: Help Please Incorrect DAX code

Re: Help Please Incorrect DAX code

Re: Help Please Incorrect DAX code