cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

Top Solution Authors
Top Kudoed Authors