Comparing year on year figures - problem with calculated column

[jonclay]

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

[jonclay]

Hi @rajulshah 

Thank you for helping me out with this - it works perfectly! Apologies for the delayed response but I've been away from work for a while.

Best wishes
Jon

[rajulshah · Accepted solution]

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