Help Please Incorrect DAX code
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!