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

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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    For this new issue, please start a new thread.