cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rpul
Frequent Visitor

DAX functions in CALCULATE

@v-sihou-msft

 

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. 

 

DAStatError.PNG

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.

1 ACCEPTED SOLUTION

Hi,

 

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

 

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


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

View solution in original post

12 REPLIES 12
Phil_Seamark
Microsoft
Microsoft

Are you using a separate Date table?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

@Phil_Seamark

 

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

 

@Interkoubess

 

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.
Ninter

@Interkoubess

 

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:

previousyearerror2.PNG

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.

Interkoubess
Solution Sage
Solution Sage

Hi @rpul,

 

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

 

 

Ninter

@Interkoubess

 

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.

 

Ninter

@Interkoubess

 

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

 

Ninter

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

sampledatapowerbi.PNG


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

previousyearerror.PNG

 

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

Hi,

 

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

 

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


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

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

Capture.PNG

Helpful resources

Announcements
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