Forum Discussion
Solved
Help with DAX code
Hi, I have an excel output of a calculated DAX measure. The Calculated Pay (N) column is a sum of AUD Total Daily Earnings column, grouped by the Applicable Payroll Date. I want the Calculated Pay (...
OK, the failure here was that your sample data was not representative of your actual data. In your actual data, we have to account for Employee_ID. So, that can be done like this:
Result = VAR __CalculatedPay = 'Sheet1'[Calculated Pay (N)] VAR __Next = MINX( FILTER( 'Sheet1', 'Sheet1'[Calculation Date] > EARLIER('Sheet1'[Calculation Date]) && 'Sheet1'[Employee_ID] = EARLIER('Sheet1'[Employee_ID]) ), 'Sheet1'[Calculation Date] ) VAR __NextPay = MINX( FILTER( 'Sheet1', 'Sheet1'[Calculation Date] = __Next && 'Sheet1'[Employee_ID] = EARLIER('Sheet1'[Employee_ID]) ), 'Sheet1'[Calculated Pay (N)] ) RETURN IF(__CalculatedPay <> __NextPay,__CalculatedPay,0)Updated PBIX is attached.
OK, I may be mistaken but I believe you will need to add an Index column to your data table. Then you should be able to use a technique similar to MTBF as desribed here: See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586
So the excel formula works but in excel. How do I translate this logic to DAX?
=IF(AND(D2>=C2,E2<>E3),E2,0)
| AUD Total Daily Earnings | CalculatedPay | Calculation Date | Applicable Payroll Date | Calculated Pay (N) | Calculated Pay (Expected) | Expected (Excel Formula) |
| $327.81 | $2,031.34 | 1/09/2013 0:00 | 1/09/2013 0:00 | 2031.34 | 2031.34 | 2031.34 |
| $178.60 | $0.00 | 4/09/2013 0:00 | 15/09/2013 0:00 | 1959.18 | 0 | 0 |
| $185.73 | $0.00 | 5/09/2013 0:00 | 15/09/2013 0:00 | 1959.18 | 0 | 0 |
| $162.02 | $0.00 | 6/09/2013 0:00 | 15/09/2013 0:00 | 1959.18 | 0 | 0 |
| $228.94 | $0.00 | 7/09/2013 0:00 | 15/09/2013 0:00 | 1959.18 | 0 | 0 |
| $292.16 | $0.00 | 8/09/2013 0:00 | 15/09/2013 0:00 | 1959.18 | 0 | 0 |
| $151.95 | $0.00 | 11/09/2013 0:00 | 15/09/2013 0:00 | 1959.18 | 0 | 0 |
| $151.96 | $0.00 | 12/09/2013 0:00 | 15/09/2013 0:00 | 1959.18 | 0 | 0 |
| $151.96 | $0.00 | 13/09/2013 0:00 | 15/09/2013 0:00 | 1959.18 | 0 | 0 |
| $189.94 | $0.00 | 14/09/2013 0:00 | 15/09/2013 0:00 | 1959.18 | 0 | 0 |
| $265.92 | $1,959.18 | 15/09/2013 0:00 | 15/09/2013 0:00 | 1959.18 | 1959.18 | 1959.18 |
| $141.82 | $0.00 | 18/09/2013 0:00 | 29/09/2013 0:00 | 2063.78 | 0 | 0 |
| $166.15 | $0.00 | 19/09/2013 0:00 | 29/09/2013 0:00 | 2063.78 | 0 | 0 |
| $271.50 | $0.00 | 20/09/2013 0:00 | 29/09/2013 0:00 | 2063.78 | 0 | 0 |
| $192.47 | $0.00 | 21/09/2013 0:00 | 29/09/2013 0:00 | 2063.78 | 0 | 0 |
| $269.46 | $0.00 | 22/09/2013 0:00 | 29/09/2013 0:00 | 2063.78 | 0 | 0 |
| $151.95 | $0.00 | 25/09/2013 0:00 | 29/09/2013 0:00 | 2063.78 | 0 | 0 |
| $151.96 | $0.00 | 26/09/2013 0:00 | 29/09/2013 0:00 | 2063.78 | 0 | 0 |
| $187.24 | $0.00 | 27/09/2013 0:00 | 29/09/2013 0:00 | 2063.78 | 0 | 0 |
| $221.65 | $0.00 | 28/09/2013 0:00 | 29/09/2013 0:00 | 2063.78 | 0 | 0 |
| $309.58 | $2,063.78 | 29/09/2013 0:00 | 29/09/2013 0:00 | 2063.78 | 2063.78 | 2063.78 |
| $151.96 | $0.00 | 30/09/2013 0:00 | 13/10/2013 0:00 | 2167.23 | 0 | 0 |
| $171.93 | $0.00 | 1/10/2013 0:00 | 13/10/2013 0:00 | 2167.23 | 0 | 0 |
| $151.95 | $0.00 | 2/10/2013 0:00 | 13/10/2013 0:00 | 2167.23 | 0 | 0 |
| $289.32 | $0.00 | 5/10/2013 0:00 | 13/10/2013 0:00 | 2167.23 | 0 | 0 |
| $333.89 | $0.00 | 6/10/2013 0:00 | 13/10/2013 0:00 | 2167.23 | 0 | 0 |
| $405.20 | $0.00 | 7/10/2013 0:00 | 13/10/2013 0:00 | 2167.23 | 0 | 0 |
| $151.96 | $0.00 | 8/10/2013 0:00 | 13/10/2013 0:00 | 2167.23 | 0 | 0 |
| $141.82 | $0.00 | 9/10/2013 0:00 | 13/10/2013 0:00 | 2167.23 | 0 | 0 |
| $187.85 | $0.00 | 10/10/2013 0:00 | 13/10/2013 0:00 | 2167.23 | 0 | 0 |
| $181.35 | $0.00 | 11/10/2013 0:00 | 13/10/2013 0:00 | 2167.23 | 2167.23 | 2167.23 |
| $169.19 | $0.00 | 14/10/2013 0:00 | 27/10/2013 0:00 | 2036.55 | 0 | 0 |
| $184.08 | $0.00 | 15/10/2013 0:00 | 27/10/2013 0:00 | 2036.55 | 0 | 0 |
| $173.75 | $0.00 | 18/10/2013 0:00 | 27/10/2013 0:00 | 2036.55 | 0 | 0 |
| $247.18 | $0.00 | 19/10/2013 0:00 | 27/10/2013 0:00 | 2036.55 | 0 | 0 |
| $285.67 | $0.00 | 20/10/2013 0:00 | 27/10/2013 0:00 | 2036.55 | 0 | 0 |
| $151.96 | $0.00 | 21/10/2013 0:00 | 27/10/2013 0:00 | 2036.55 | 0 | 0 |
| $151.96 | $0.00 | 22/10/2013 0:00 | 27/10/2013 0:00 | 2036.55 | 0 | 0 |
| $151.96 | $0.00 | 23/10/2013 0:00 | 27/10/2013 0:00 | 2036.55 | 0 | 0 |
| $254.88 | $0.00 | 26/10/2013 0:00 | 27/10/2013 0:00 | 2036.55 | 0 | 0 |
| $265.92 | $2,036.55 | 27/10/2013 0:00 | 27/10/2013 0:00 | 2036.55 | 2036.55 | 2036.55 |
| $202.35 | $0.00 | 30/10/2013 0:00 | 10/11/2013 0:00 | 1928.3 | 0 | 0 |
| $190.45 | $0.00 | 31/10/2013 0:00 | 10/11/2013 0:00 | 1928.3 | 1928.3 | 1928.3 |