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!
