Help with DAX code

Thanks a ton Greg!

This works.
If you can please explain the code, i would understand it much better.

Harshad

Sure, harshad_barge glad we got there. I've probably spent a good 3 or 4 hours on this over the last couple days so an extra 15 minutes isn't going to break the bank. 🙂

Here is the code again for reference:

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)

So, this is a column thus we are in row context. The first line just grabs the value for the Calculated Pay (N) column in the current row and stores this in the variable __CalculatedPay.

Next we create the variable __Next. The purpose of this variable is to find the minimum next Calculation Date in the table that is greater than the current row's value for Calculation Date for the same employee that is in our current row. So, we FILTER our table using EARLIER. I described how EARLIER works earlier in the thread so I won't belabour how this works. We use MINX to grab the lowest (earliest - don't get confused) date from that filtered set. The filtered set includes all rows in the table for the current employee that have a Calculation Date that is greater than the current row's value for Calculation Date. 

Next, we use the same basic technique to find the value for the Calculated Pay (N) column that corresponds with the employee from our current row and the __Next date we just calculated. We store this value in the variable __NextPay.

In our RETURN statement, we now compare the value of our current row __CalculatedPay with the value of __NextPay. If the value is different, we return __CalculatedPay. If the value is the same, we return 0. The overall effect is that if we are at the maximum Calculated Date for a consistent Calculated Pay (N), then we return the value in our Calculated Pay (N) column for the current row because the very next Calculated Date in our table for the current employee the value for Calculated Pay (N) has changed. If we are in a row that is not the maximum Calculated Date for a consistent Calculated Pay (N) then we return 0 because the next Calculated Pay (N) is the same as our current row's value for Calculated Pay (N).

Hopefully that is clear. The logic is perhaps a bit tough to wrap your head around which is why this problem was very challenging.

I don't see your column Calculation Date, where is that column? This is a screen shot from the PBIX I sent and am again attaching here that takes your sample data and replicates exactly the column you requested. The Result column is exactly the same as the Expected (Excel Formula). If however your Calculation Dates are descending instead of ascending like in your example data that could likely be the cause of the issue.

Hi Greg,

Please find the dataset with anonymised names. 
The logic is essentially checking when the sum of "CalculatedPay" should be paid which is on/closest (before) to "Applicable Payroll Date".
The "Calculated Pay" column is missing that sum when the employee did not work on the applicable payroll date. and the Calculated Pay (N) column is doing the job but repeating values.
Please find the dataset here

Thanks !