cancel
Showing results 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

Anonymous
Not applicable

## Previous Period Calculation Help

Hello,

I'm trying to write a DAX formula to get the previous year data in my table.  Currently I have the following table:

My formula for my "Test" column is:

Test = CALCULATE(sum([Actual]), DATEADD([CalendarDate], -1, YEAR))

However, my "Test" column appears blank when I'd expect the row for 2012 to show the actual value for 2011, the 2013 row to show the value for 2012, etc.  This seems simple as I've done it so many times before, but for some reason I can't get this one working.  Any idea what may be wrong with my formula here?

1 ACCEPTED SOLUTION
Super User

Hi,

That formula should be written as a measure (not as a calculated column).  You may also try this measure

=CALCULATE(SUM([Actual]),SAMEPERIODLASTYEAR(Calendar[CalendarDate]))

In your visual, drag any/all time dimensions only from the Calendar Table.  Also, ensure that there is a relationship from the Date column of the Data Table to the CalendarDate column of the Calendar Table.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
3 REPLIES 3
Super User

Hi,

That formula should be written as a measure (not as a calculated column).  You may also try this measure

=CALCULATE(SUM([Actual]),SAMEPERIODLASTYEAR(Calendar[CalendarDate]))

In your visual, drag any/all time dimensions only from the Calendar Table.  Also, ensure that there is a relationship from the Date column of the Data Table to the CalendarDate column of the Calendar Table.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable

Hi Ashish,

Thanks for your formula.  Actually, your formula and my formula both did the trick.  I was just referencing the date column from my date table and then placing the date column from my fact table into the visual.  Once I fixed that, I was able to get the results properly.

Super User

Regards,
Ashish Mathur
http://www.ashishmathur.com

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.

Top Solution Authors
Top Kudoed Authors