Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

DAX functions in CALCULATE



I have been trying to carry out a calculation comparing previous years, but whenever I use a DAX function, such as PREVIOUSYEAR, as a filter in CALCULATE nothing gets returned. 



My current expression is:

Difference = CALCULATE(SUM('Yearly Summary'[Sales by Status per Year]), PREVIOUSYEAR('Yearly Summary'[Date].[Date]))


But this returns just blanks. Any idea why this may be or ideas for trouble shooting? If I drop the filter, it works. If I try other filters that are not DAX functions such as 'Yearly Summary'[Date].[Date]=2017 it works.




In your visual drag the Year from the Calendar Table.  Write this measure


=CALCULATE(SUM(sheet1[NUmber of sales]),PREVIOUSYEAR(Calendar[Date]))

Ashish Mathur

View solution in original post


Are you using a separate Date table?

To learn more about DAX visit :

Proud to be a Datanaut!



I've tried using a separate date table and simply using the date column I have in the actual data, neither works. 




I've tried adding the additional columns you suggested, the problem persists. It's also not clear to me why the additional columns would matter as previousyear is just drawing on the original date column, not drawing on any of the added columns such as 'YEAR'.

Hi @rpul,

It was a proposition.
But if you want to see the result on a yearly basis then you need to show the date hierarchy or take it from the calendar table ( and it is an easy one) to get this presentation.

Let us know if you have observations.



This issue, especially as you were able to use the sample data and get it to work is very strange to me (and I find the inability to look for errors in power bi pretty frustrating in general). One thing to note is that when I do connect the calendar table and the fact table, power bi actually breaks up the summarizing by date and department:


I don't think this should be impacting the measure and to deal with this I just create a year column and use that instead. Suggestions on other DAX functions to test? Again, I've encountered this issue with both PREVIOUSYEAR and GROUPBY, but since I can't get anything to display in my New column I haven't been able to figure out what's going wrong other than what seems to be an interaction between CALCULATE and the DAX function.

Solution Sage
Solution Sage

Hi @rpul,


Do you have a c alendar date linked to your data?






If by calendar date, you mean a date column, yes that is what the 'Yearly Summary' column is.

Nope @rpul,


I mean a distinct calendar table linked to your fact table by date.

Most of time intelligence functions work with a calendar table.





I do not have a distinct calendar table. After looking at documentation for a Calendar Table and the documentation for functions like PREVIOUSYEAR it's not clear to me how this could help me. I have a date column in my table already, as required by the function, and I also have year and month columns, but using these does not work as the function documentation states it should. It also doesn't answer the question of why other DAX functions, such as GROUPBY also return blanks.

Hi @rpul,


Please create a calendar table and link it to your data model and then use the formula ( previousyear), it should work.

If not please share dummy data and I will make a try.


Thank you



@Interkoubess thank you for your help.


I created a calendar table (New Table, Calendar = CALENDAR(DATE(2011,1,1), TODAY())) and linked this to my data and still nothing appears. Here is some dummy data where I still do not get any output:


And here is the output when I try to get the previousyear function to run (again using a calendar calculated table or using the date column): 



I'd like the new column to be ( , , , 11, 15, 20, 25, 31, 35)



In your visual drag the Year from the Calendar Table.  Write this measure


=CALCULATE(SUM(sheet1[NUmber of sales]),PREVIOUSYEAR(Calendar[Date]))

Ashish Mathur

View solution in original post

Hi @rpul,


You can add more fields to your calendar table like this :

Date = ADDCOLUMNS ( CALENDAR (DATE(2000,1,1), DATE(2025,12,31)), "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ), "Year", YEAR ( [Date] ), "MonthNo", FORMAT ( [Date], "MM" ), "YearMonthNo", FORMAT ( [Date], "YYYY/MM" ), "YearMonth", FORMAT ( [Date], "YYYY/mmm" ), "MonthShort", FORMAT ( [Date], "mmm" ), "MonthLong", FORMAT ( [Date], "mmmm" ), "WeekNo", WEEKDAY ( [Date] ), "WeekDay", FORMAT ( [Date], "dddd" ), "WeekDayShort", FORMAT ( [Date], "dddd" ), "Quarter", "Q" & FORMAT ( [Date], "Q" ), "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )

Then link it to your fact model like this and create the following measure:

New = CALCULATE(SUM(Sheet1[Number of sales]),PREVIOUSYEAR('Calend'[Date]))Capture1.PNG


Helpful resources

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors