Forum Discussion
Modifying DAX query to extract correct value
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]
)
)
=IF(AND(E12>=D12,F12<>F13),F12,0)
| 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))
)
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!
5 Replies
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!! 🙂
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.No.
