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

Reply
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:

 

Capture.PNG

 

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
Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/
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.

 

Thanks for your help!

You are welcome.  If my reply helped, please mark it as Answer.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Europe Fabric Conference

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.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

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