Referencing Prior Quarters

[Sax]

Hello everyone,

hope you can help me. I got a simple data model with some Sales data, a Calendar and a Dimension table connected to it.

Key to know is the granularity of my fact table: I only got data at Quarter level (no months, no days). The column Year states always the beginning of each Quarter. It looks like the small sample below:

Product type	Year	Quarter	Value (USD)
A	01/01/2019	Q1-19	100
B	01/04/2020	Q2-20	67
A	01/10/2022	Q4-22	83

What I'm trying to achieve is the outcome on the Prior Q column, that is a reference to the previous Q without going into uber convoluted Dax expressions, which is the only way I got the following result:

To be clear, I have no problems in getting the right sales data within this filter context, I just want the reference to the Quarter and the Year.

What I managed to get is the outcome on last year's quarter (column LY Q) with the following expressions:

1. Last Q =

MAX('Calendar'[Quarter/Year])

2. LY Q =
CALCULATE([Last Q],
SAMEPERIODLASTYEAR('Calendar'[Dates]))

But when it comes to the Prior Q, I'm lost being a beginner in DAX.

Hope I've been clear.

Thanks for your help! 

[Sax · Accepted solution]

Hi all,

the best solution I could find is to build the following measure:

Prior Q =
-- Setting Quarter variables
VAR PriorQNumber =
    QUARTER ( MAX ( 'Calendar'[Dates] ) ) - 1 -- Returns last Quarter number minus 1: 0,1,2,3
VAR PriorQNumberCorrection =
    IF ( PriorQNumber = 0, 4, PriorQNumber ) -- Replacing 0 to 4
VAR PriorQuarter = "Q" & PriorQNumberCorrection -- Returns prior Quarter formatted as Q1, Q2, Q3, Q4

-- Setting Year variables 
VAR LatestY =
    RIGHT ( YEAR ( MAX ( 'Calendar'[Dates] ) ), 2 ) -- Returns last Year formatted as "19" "20" etc 
VAR PriorY =
    RIGHT ( LatestY - 1, 2 ) -- Returns prior Year before last
RETURN
    IF (
        PriorQuarter = "Q4",
        PriorQuarter & "-" & PriorY,
        PriorQuarter & "-" & LatestY
    )

[Ashish_Mathur]

Hi,

Create a Calendar Table with a relationship (Many to One and Single) from the year column to the Date column.  In the Calendar Table, create these calculated column formulas

Year = year(Calendar[Date])

Month number = month(Calendar[Date])

Quarter = if(Calendar[month number]>=10,"Q4",if(Calendar[month number]>=7,"Q3",if(Calendar[month number]>=4,"Q2","Q1")))

To your visual, drag Year and Quarter from the Calendar Table.  Write these measures

Total = sum(Data[Sales])

Total in previous quarter = calculate([Total],previousquarter(Calendar[date]))

Hope this helps.

[Sax]

Hello @Ashish_Mathur 

thanks for your reply. Unfortunately, is not what I'm after. I do have a calendar and if anything, as I'm looking at Prior Quarter, I could add a colum into my Calendar with the following:

 
Quarter Prior = IF('Calendar'[Month number]>=10,"Q3",
IF('Calendar'[Month number]>=7,"Q2",
IF('Calendar'[Month number]>=4,"Q1","Q4")))

As mentioned, however I do not have an issue in calculating sales or % change for prior quarter - I got a measure that works fine. What I need is the text reference to the prior quarter which I need to put into a card's subtitle - see below. It is the second card's Q3-22 text reference I'm struggling with. The %s themeselves are correct.

Thanks    

[Ashish_Mathur]

I misunderstood your question.  Share the download link of the PBI file.

[Sax]

Hi,

Apologies for the late reply. As it is a file from work, I had to do some edits – I'm sharing the file through OneDrive https://1drv.ms/u/s!AjvfcnbgheIelU3bn91GQ6Hu9VoK?e=Jdktfa

You'll see 2 cards – Card 1 works fine, Card 2 is where I had the issue with the subtitle but I think I sorted it now. I built a measure named Prior Q/Y. It is a bit convoluted but couldn’t think of any better – Seems to be working but any advice to simplify it are welcomed!

However, I’ve discovered there is an additional issue with the callout value of Card 2 for which I don’t know if I should raise this in a separate message.

What I’m trying to achieve is whatever is stated in title/subtitle:

• when no filter is selected, then the value should reflect what is stated in the card that is Q4-22 compared to Q3-22 – the correct value should be 0.7%
• when year is selected, I’d like to see the % change of the latest quarter of that year compared to the prior Q. Right now, is displaying % change at year level
• when quarter is selected, I’d like to see that quarter % change of the latest year I have data for – i.e. if I select Q3 , the result should be 1% (Q3-22 over Q2-22)

Hope is clear.

Thanks

[Ashish_Mathur]

For this new issue, please start a new thread.

[Sax]

Thanks for your reply Pat.

I've created a table using the Calendar table: 

Quarters = DISTINCT('Calendar'[Quarter/Year])

However, I'm finding it difficult to add an Index column. Some solutions involve using Rankx..but couldn't make it work. Any suggestions?
Thanks 

[ppm1]

Have you considered adding a Quarters table (like a date table but just one row per quarter) with a quarter index value (e.g., qtrs from today or from start)? That would allow you to easily do -1 type calculations. The table would have two columns (qtr index and the other with your values like Q1-21, etc. for the relationship to your fact table).

Pat