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 t...
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
        )
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,

         

        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