Last Quarter

[Anonymous]

Hi All,

I have a column which contains different Quarters and year for example 2018Q4. This quarter was derived from an[ Event date] Column and has been made to be a slicer.  There's a Count column in the table which aggregates the data for the quarters.

For example, using a matrix report and selecting a region and the count from the table and selecting any of the quarters from the slicer, the data comes back correct for the quarter selected. The issue now is that I want to be able to write a DAX which gives me the data for the previous Quarter.

I tried this but it comes back with error in the matrix:

LastQtr = CALCULATE(SUM(SumQuart[Count]), DATEADD(SumQuart[Event date], -1, QUARTER))

This calculation does not give any syntax error but inserting the measure into the matrix throws up 'Cant display the visual' error.  Inserting it into a new matrix shows the data for the quarter selected in the slicer and not for the previous quarter.  What exactly am I doing wrong?

[kcantor]

@Anonymous Are you using a seperate Date table? You seem to be using time intelligence and applying it on a field from your fact table.

When I use Date Add, I reference my date table and the key that the date from the fact table is related to. For example, using my date table, I would use this for your dax: LastQtr = CALCULATE(SUM(SumQuart[Count]), DATEADD(DimDate[DateKey], -1, QUARTER))

My date key would be my contingious date field with the relationship to my fact table.

[Anonymous]

I do not have a separate Date table but the date column from my field. So this requires me to have a separate date table?

[kcantor]

Generally speaking a date table is a must to ensure the date range has no gaps. See the documentation provided here: https://www.tutorialspoint.com/excel_dax/excel_dax_time_intelligence.htm
This also gives excellent measure building advice.  Once you add a date table and change the formula to reference it, you should get the results you desire. 

[Anonymous]

I have created a separate Date table using a Calendar table in our Database. I have created a relationship between this table and the SumQuart table which has a column [Event Date]. My formular has changed as follows:

PreviousYEAR = CALCULATE(SUM(SumQuart[Count]),DATEADD('Date'[Date], -1, YEAR))

However, the data that comes back is still the same as any YearQtr selected in the Slicer. The slicer has this format 2017Q1, 2017Q2 etc. I expect to see data for 2016Q1 when I select 2017Q1 in the slicer and  if I drag the Previousyear column into the matrix.

Any Idea? 

[kcantor · Accepted solution]

@Anonymous 

Are you using a quarter year column from your date table or are you populating that field from your fact table? You should always populate your Slicers, Rows, or axis from the dimension table and only in very rare cases should you populate it from your fact table. The date table must be used in order for the calculation to work correctly.

As @novilpawar  suggested, you can use SAMEPERIODLASTYEAR as well but it is simply a wrapper to make the DATEADD/Time Intelligence easier to use. The results should be the same and if DATEADD is not working SAMEPERIODLASTYEAR will not work corretly either.

[novilpawar]

hii @Ejyks

Please check with  SAMEPERIODLASTYEAR(), if you want same quarter in last year.

Regards,

Novil