Help with DAX code

[harshad_barge]

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 (Expected column to show the value only when the Applicable Payroll Date changes.
Many Thanks.

AUD Total Daily Earnings	CalculatedPay	Calculation Date	Applicable Payroll Date	Calculated Pay (N)	Calculated Pay (Expected)
$327.81	$2,031.34	1/09/2013 0:00	1/09/2013 0:00	2031.34	2031.34
$178.60	$0.00	4/09/2013 0:00	15/09/2013 0:00	1959.18	0
$185.73	$0.00	5/09/2013 0:00	15/09/2013 0:00	1959.18	0
$162.02	$0.00	6/09/2013 0:00	15/09/2013 0:00	1959.18	0
$228.94	$0.00	7/09/2013 0:00	15/09/2013 0:00	1959.18	0
$292.16	$0.00	8/09/2013 0:00	15/09/2013 0:00	1959.18	0
$151.95	$0.00	11/09/2013 0:00	15/09/2013 0:00	1959.18	0
$151.96	$0.00	12/09/2013 0:00	15/09/2013 0:00	1959.18	0
$151.96	$0.00	13/09/2013 0:00	15/09/2013 0:00	1959.18	0
$189.94	$0.00	14/09/2013 0:00	15/09/2013 0:00	1959.18	0
$265.92	$1,959.18	15/09/2013 0:00	15/09/2013 0:00	1959.18	1959.18
$141.82	$0.00	18/09/2013 0:00	29/09/2013 0:00	2063.78	0
$166.15	$0.00	19/09/2013 0:00	29/09/2013 0:00	2063.78	0
$271.50	$0.00	20/09/2013 0:00	29/09/2013 0:00	2063.78	0
$192.47	$0.00	21/09/2013 0:00	29/09/2013 0:00	2063.78	0
$269.46	$0.00	22/09/2013 0:00	29/09/2013 0:00	2063.78	0
$151.95	$0.00	25/09/2013 0:00	29/09/2013 0:00	2063.78	0
$151.96	$0.00	26/09/2013 0:00	29/09/2013 0:00	2063.78	0
$187.24	$0.00	27/09/2013 0:00	29/09/2013 0:00	2063.78	0
$221.65	$0.00	28/09/2013 0:00	29/09/2013 0:00	2063.78	0
$309.58	$2,063.78	29/09/2013 0:00	29/09/2013 0:00	2063.78	2063.78
$151.96	$0.00	30/09/2013 0:00	13/10/2013 0:00	2167.23	0
$171.93	$0.00	1/10/2013 0:00	13/10/2013 0:00	2167.23	0
$151.95	$0.00	2/10/2013 0:00	13/10/2013 0:00	2167.23	0
$289.32	$0.00	5/10/2013 0:00	13/10/2013 0:00	2167.23	0
$333.89	$0.00	6/10/2013 0:00	13/10/2013 0:00	2167.23	0
$405.20	$0.00	7/10/2013 0:00	13/10/2013 0:00	2167.23	0
$151.96	$0.00	8/10/2013 0:00	13/10/2013 0:00	2167.23	0
$141.82	$0.00	9/10/2013 0:00	13/10/2013 0:00	2167.23	0
$187.85	$0.00	10/10/2013 0:00	13/10/2013 0:00	2167.23	0
$181.35	$0.00	11/10/2013 0:00	13/10/2013 0:00	2167.23	2167.23
$169.19	$0.00	14/10/2013 0:00	27/10/2013 0:00	2036.55	0
$184.08	$0.00	15/10/2013 0:00	27/10/2013 0:00	2036.55	0
$173.75	$0.00	18/10/2013 0:00	27/10/2013 0:00	2036.55	0
$247.18	$0.00	19/10/2013 0:00	27/10/2013 0:00	2036.55	0
$285.67	$0.00	20/10/2013 0:00	27/10/2013 0:00	2036.55	0
$151.96	$0.00	21/10/2013 0:00	27/10/2013 0:00	2036.55	0
$151.96	$0.00	22/10/2013 0:00	27/10/2013 0:00	2036.55	0
$151.96	$0.00	23/10/2013 0:00	27/10/2013 0:00	2036.55	0
$254.88	$0.00	26/10/2013 0:00	27/10/2013 0:00	2036.55	0
$265.92	$2,036.55	27/10/2013 0:00	27/10/2013 0:00	2036.55	2036.55
$202.35	$0.00	30/10/2013 0:00	10/11/2013 0:00	1928.3	0
$190.45	$0.00	31/10/2013 0:00	10/11/2013 0:00	1928.3	0

[Anonymous]

I think your table is a table in the model, not a visual. So please use Power Query to do what you want. It's not only much much simpler but it IS THE WAY to do it.

Best
D

[Greg_Deckler]

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/3395...

[harshad_barge]

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

[Greg_Deckler]

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.

[harshad_barge]

🙂

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]

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]

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]

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

[Greg_Deckler]

@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)

[harshad_barge]

Hi Greg,

Sorry for the delayed response.

However, your DAX does not seem to work. It is still repeating values. 

That being said, i am intrigued how does the

EARLIER 

function work? Looks like we can use it to substitute the "row +1 " as excel does but, it seems to be not an efficient one for big datasets. (I will keep that in mind)
I am sorry for replicating the question. Really sorry.
However, I noted, i did not really frame my querstion and the example dataset completely. Hence, i saw the need to post fresh. Point taken to avoid doing that!.

Let me clarify further; the dataset i posted is a sample dataset of employees being paid. essentially, many employees get paid on any given day (Applicable Payroll Date and Calculation Date columns) hence, I am a bit unsure how would that work (treating that column as index)
Its a really tricky one right?
I love these challenges.
Thanks so much Greg! We will get to the solution. I can smell it!

[Greg_Deckler]

@harshad_barge in the example PBIX file attached to my previous message, my column did not repeat values. Can you explain that in more detail? It seems to return exactly what you want in the column.

With regards to EARLIER, easily the worst named DAX function in existence. You should think of it is "current row". The reason it is called earlier is that it is referring to an "earlier" context than the one that you are creating. So, when you say something like:

FILTER(ALL('Table'),[Column] = EARLIER([Column]))

The way to read this is "create a new filter context using all of the rows in the table and then within this new context check each row to see if the [Column] in this new context matches the [Column] from the EARLIER context (which would be row context if this is a column).

[harshad_barge]

Hi Greg,

Please find the screenshot of the column I replicated.

The Result column is still repeating.

[Greg_Deckler]

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]

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 !

[Greg_Deckler · Accepted solution]

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]

Thanks a ton Greg!

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

Harshad

[Greg_Deckler]

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.