Forum Discussion

Sax's avatar
Sax
Helper I
3 years ago
Solved

Referencing Prior Quarters

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 typeYearQuarterValue (USD)
A01/01/2019Q1-19100
B01/04/2020Q2-2067
A01/10/2022Q4-2283

 

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! 

 

Need Help
  • Sax's avatar
    Sax
    3 years ago

    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
        )

8 Replies

  • ppm1's avatar
    ppm1
    Solution Sage
    3 years ago

    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

  • Sax's avatar
    Sax
    Helper I
    3 years ago

    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 
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    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's avatar
      Sax
      Helper I
      3 years ago

      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's avatar
        Ashish_Mathur
        Super User
        3 years ago

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

  • Sax's avatar
    Sax
    Helper I
    3 years ago

    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
        )