cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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 ),

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 ),

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,

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,

Hope this works. Let me know if this didn't work.

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors