Forum Discussion
Help with DAX code
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 Excel <> Power BI. Power BI cannot reference cells. But, pasting your Excel formula here might help. Power BI does not deal with cells, you have to filter your way to victory.
🙂
Here is the formula:
=IF(AND(D2>=C2,E2<>E3),E2,0)
Checks if the column is greater than other however, it also checks if previous row is equal to the next row (in the same column) So that is the catch that I would use help figuring out in DAX.
