Forum Discussion

harshad_barge's avatar
harshad_barge
Helper I
6 years ago
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 (...
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    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.

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

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.

 

 

 

harshad_barge's avatar
harshad_barge
Helper I
6 years ago

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 !