Modifying DAX query to extract correct value

harshad_barge — Fri, 28 Feb 2020 06:12:52 GMT

Hi all,
So I am essentially trying to have a column that is grouped by sum of payment. I want just the cumulative Pay when the payroll date is greater than or equal to the Calculation Date. The grouped values are correct but are repeating hence, when I show it in a table, and sum it, it gives an incorrect value.

The DAX for calculated Pay (N) used is:

Calculated Pay (N) = 

CALCULATE (

SUM ( 'Ordinary Hours'[AUD Total Daily Earnings] ),

ALLEXCEPT (

'Ordinary Hours',

'Ordinary Hours'[Employee Name],

'Ordinary Hours'[Applicable Payroll Date].[Date]

)

)

The excel formula that works to desired effect is:

=IF(AND(E12>=D12,F12<>F13),F12,0)

The problem I am facing is that I cannot use it (owing to my limited capabilities; I am quite new to DAX) in DAX as there seems no way to reference previous row

The table and the expected output is as below:

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

How can I modify my Dax to ensure that the output is as per Expected (Excel Formula) column.

p.s. The column that is incorrectly (for intents and purposes) pulling the "Calculated Pay" column is:

CalculatedPay = IF

                (

                    [Calculation Date] < [Applicable Payroll Date],

                    --then--

                    0, 

                    --else--

                    VAR payrollId = [Payroll Day ID]

                    VAR empID = [Employee_ID]

                    RETURN CALCULATE(SUM('Ordinary Hours'[AUD Total Daily Earnings]),FILTER('Ordinary Hours',[Payroll Day ID]=payrollId && [Employee_ID]=empID))    

                )

There are a few instances when the calculation date is being skipped at the applicable payroll date hence, the CalculatedPay sum is being skipped and the overall aggregation is thus incorrect.

Greg_Deckler Grateful for your help so far. I am just not getting there with what I want. I hope this post helps better.

Thank you all!

Re: Modifying DAX query to extract correct value

Greg_Deckler — Fri, 28 Feb 2020 21:40:39 GMT

It seems like I have seen this problem before, is there another similar thread that you posted? If so can you post a link to it so that I can reference it and what I did? I'll take a look at this but there are a lot of dates I need to correct because they don't work with my US English Power BI Desktop. I need to create a Power Query function to convert dates. So annoying!! 🙂

Re: Modifying DAX query to extract correct value

Anonymous — Sat, 29 Feb 2020 10:04:56 GMT

If you are trying to add a calculated column to a table in the model, then please spare yourself pain and grief - USE POWER QUERY.

Best
D

Re: Modifying DAX query to extract correct value

harshad_barge — Sun, 01 Mar 2020 22:25:57 GMT

Is there any way I can use a DAX calculated table in power query?
If that would be the case, things would be much easier.

Re: Modifying DAX query to extract correct value

Greg_Deckler — Sun, 01 Mar 2020 22:37:57 GMT

No.

Re: Modifying DAX query to extract correct value

harshad_barge — Sun, 01 Mar 2020 22:40:12 GMT

Unfortunately as I had thought!
Power BI is made quite good then! Because, if it would go back and forth, I would be in a mess! 😀

topic Re: Modifying DAX query to extract correct value in DAX Commands and Tips

Modifying DAX query to extract correct value

Re: Modifying DAX query to extract correct value

Re: Modifying DAX query to extract correct value

Re: Modifying DAX query to extract correct value

Re: Modifying DAX query to extract correct value

Re: Modifying DAX query to extract correct value