Calculate not working for getting Quarter over Quarter values

[YukiK]

Hi all,

I have a data model that has a fact table containing a measurement like sales amount. I have a calendar table whose grain is month, and the table doesn't have the date, but has relative period/month, quarter, quarter + year, etc.

I'm trying to get sales amount last quarter so as to get QoQ %, but the measure I created doesn't seem to work. The fact table is connected is at the month level on "Period" column to the calendar table on "Period" column.

I have a sales measure like this.

Sales amount = SUM(fact[sales amount])

And the QoQ measure that I'm trying to work out is like the following.

Sales amount last quarter =
VAR __CurrQuarter = MIN(calendar[relative_quarter]) -- unique for each quarter
VAR __SalesLastQuarter = 
    CALCULATE(
        [Sales amount],
        calendar[relative_quarter] = __CurrQuarter  - 1
    )
RETURN
__SalesLastQuarter 

But this measure only returns blank even though __CurrQuarter - 1 returns the correct values... Any help would be appreciated!

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your data model looks like but please try the below.

Sales amount last quarter =
VAR __CurrQuarter =
MIN ( calendar[relative_quarter] ) -- unique for each quarter
VAR __SalesLastQuarter =
CALCULATE (
[Sales amount],
FILTER ( ALL ( calendar ), calendar[relative_quarter] = __CurrQuarter - 1 )
)
RETURN
__SalesLastQuarter

[YukiK]

Okay, this worked! Using "FILTER()" around the condition was the trick. Though I don't fully understand why I needed that for this to work. It was my understanding that the interals would be the same with that of without "FILTER()".

Would you mind sharing your wisdom around this concept?

[Jihwan_Kim]

Hi, 

it is quite difficult for me to explain without seeing the model.

Thank you.

[YukiK]

- I have a sales fact table whose grain is per month per row.

- I have a dimention calendar table whose grain is also per month per row.

What might be tricky is my calculation is for quarter level data.