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.

     

harshad_barge's avatar
harshad_barge
Helper I
6 years ago

🙂

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.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

Right, you will definitely want an index column. Otherwise, Power BI is going to have no idea if one row comes before another. If you have an index column, you can do something like this for a calculated column in a table:

 

Column =
  VAR __NextID = [Index] + 1
  VAR __NextDate = MAXX(FILTER('Table',[Index] = __NextID),[Applicable Payroll Date])
RETURN
  IF([Applicable Payroll Date] <> __NextDate,<<some calculation goes here>>,BLANK())
  • harshad_barge's avatar
    harshad_barge
    Helper I
    6 years ago

    Awesome! 
    Firstly, how do I go about building that Index column. Note, this is a calculated measure hence I cannot go back to edit queries and add index there.

    Thanks,

    Harshad

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      6 years ago

      Wait, why can't you add an Index column in your query? This is a calculated table?

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        6 years ago

        harshad_barge - You have essentially posted the same question to multiple threads. That is not good. It confuses things as you have different information in different threads. Makes it very difficult to help you. It is specifically called out as improper etiquette here How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490. If you need to give a thread a visibility "bump", please post a new forum message with a link back to the original thread.

         

        So, with all that said, I took a closer look at this, and this took a little bit of time because I had to convert your dates, but it seems that we can use your Calculation Date as a substitute for an Index. So I ended up with the below code. PBIX is attached.

         

        Result = 
    VAR __CalculatedPay = 'Table'[Calculated Pay (N)]
    VAR __Next = 
        MINX(
            FILTER(
                'Table',
                'Table'[Calculation Date] > EARLIER('Table'[Calculation Date])
            ),
            'Table'[Calculation Date]
        )
    VAR __NextPay = 
        MINX(
            FILTER(
                'Table',
                'Table'[Calculation Date] = __Next
            ),
            'Table'[Calculated Pay (N)]
        )
RETURN
    IF(__CalculatedPay <> __NextPay,__CalculatedPay,0)