Forum Discussion
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.
Hi,
In your visual drag the Year from the Calendar Table. Write this measure
=CALCULATE(SUM(sheet1[NUmber of sales]),PREVIOUSYEAR(Calendar[Date]))
12 Replies
InterkoubessSolution Sage
If by calendar date, you mean a date column, yes that is what the 'Yearly Summary' column is.
- Interkoubess
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
- Phil_Seamark
Microsoft Employee
Are you using a separate Date table?
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'.
- InterkoubessHi 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
