Forum Discussion
Solved
Comparing year on year figures - problem with calculated column
Hi everyone
I have a calculated column that works out the total of various financial transactions between dates. This is shown below and is working fine:
Total_NEW_P3_DC3 = IF ( TR_3_DC3_Burs[pledges.sic_pledgedate] >= DATE ( 2022, 9, 1)
&& TR_3_DC3_Burs[pledges.sic_pledgedate] < DATE ( 2023, 9, 1 )
&& TR_3_DC3_Burs[statuscodename] = "Paid"
&& TR_3_DC3_Burs[si_transactiondateofpayment] >= DATE ( 2022, 9, 1 )
&& TR_3_DC3_Burs[si_transactiondateofpayment] <= DATE ( 2023, 8, 31 ),
( TR_3_DC3_Burs[sic_dc3totalincgiftaid] ))
I then need another column that works out the same figures but for 1 year earlier and to-date (i.e. upto today but 1 year ago). I've therefore changed the formula to show TODAY()-365) but I'm getting an error.
PrevYr_Total_NEW_P3_DC3 =
IF ( TR_3_DC3_Burs[pledges.sic_pledgedate] >= DATE ( 2021, 9, 1)
&& TR_3_DC3_Burs[pledges.sic_pledgedate] < DATE ( Today( )-365 )
&& TR_3_DC3_Burs[statuscodename] = "Paid"
&& TR_3_DC3_Burs[si_transactiondateofpayment] >= DATE ( 2021, 9, 1 )
&& TR_3_DC3_Burs[si_transactiondateofpayment] <= DATE ( Today( )-365 ),
( TR_3_DC3_Burs[sic_dc3totalincgiftaid] ))
Could anyone please tell me what I'm doing wrong?
Many thanks
Jon
Could anyone please tell me what I'm doing wrong?
Many thanks
Jon
jonclay ,
You can write the DAX as below:
PrevYr_Total_NEW_P3_DC3 = VAR PrevYearDay = TODAY()-365 RETURN IF ( TR_3_DC3_Burs[pledges.sic_pledgedate] >= DATE ( 2021, 9, 1) && TR_3_DC3_Burs[pledges.sic_pledgedate] < PrevYearDay && TR_3_DC3_Burs[statuscodename] = "Paid" && TR_3_DC3_Burs[si_transactiondateofpayment] >= DATE ( 2021, 9, 1 ) && TR_3_DC3_Burs[si_transactiondateofpayment] <= PrevYearDay, ( TR_3_DC3_Burs[sic_dc3totalincgiftaid] ))Hope this works. Let me know if this didn't work.
2 Replies
jonclay ,
You can write the DAX as below:
PrevYr_Total_NEW_P3_DC3 = VAR PrevYearDay = TODAY()-365 RETURN IF ( TR_3_DC3_Burs[pledges.sic_pledgedate] >= DATE ( 2021, 9, 1) && TR_3_DC3_Burs[pledges.sic_pledgedate] < PrevYearDay && TR_3_DC3_Burs[statuscodename] = "Paid" && TR_3_DC3_Burs[si_transactiondateofpayment] >= DATE ( 2021, 9, 1 ) && TR_3_DC3_Burs[si_transactiondateofpayment] <= PrevYearDay, ( TR_3_DC3_Burs[sic_dc3totalincgiftaid] ))Hope this works. Let me know if this didn't work.
