DAX functions in CALCULATE

[rpul]

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

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.

[Phil_Seamark]

Are you using a separate Date table?

[rpul]

@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'.

[Interkoubess]

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

[rpul]

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

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]

Hi @rpul,

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

Ninter

[rpul]

@Interkoubess

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

[rpul]

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

[Interkoubess]

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

[rpul]

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

[Ashish_Mathur · 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]))

[Interkoubess]

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