Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

## 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
1 ACCEPTED SOLUTION
Super User

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 2
Helper IV

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

Super User

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.

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors