Forum Discussion

vf88's avatar
vf88
Regular Visitor
1 year ago
Solved

Quarter over Quarter Line graph

I have this visual (bar and line graph). I want to have the line graph  as the QoQ change in % of the value. The quarter end for my companies are Q1: january, Q2: April. Q3: July and Q4: October. Every method I have tried for QoQ has retruned blank or 0. Any advice would be appreciated.

 

 

My Calcualted table is: 

FiscalDateTable =
ADDCOLUMNS(
    CALENDAR(DATE(2023, 1, 1), DATE(2027, 12, 31)),
    "Fiscal Year", IF(MONTH([Date]) > 10, YEAR([Date]) + 1, YEAR([Date])),
    "Fiscal Quarter",
        SWITCH(
            TRUE(),
            MONTH([Date]) IN {11, 12, 1}, "Q1",
            MONTH([Date]) IN {2, 3, 4}, "Q2",
            MONTH([Date]) IN {5, 6, 7}, "Q3",
            MONTH([Date]) IN {8, 9, 10}, "Q4"
        ),
    "Fiscal Year-Quarter",
        CONCATENATE(
            IF(MONTH([Date]) > 10, YEAR([Date]) + 1, YEAR([Date])),
            " " & SWITCH(
                TRUE(),
                MONTH([Date]) IN {11, 12, 1}, "Q1",
                MONTH([Date]) IN {2, 3, 4}, "Q2",
                MONTH([Date]) IN {5, 6, 7}, "Q3",
                MONTH([Date]) IN {8, 9, 10}, "Q4"
            )
        )
)

 

Need Help
  • vf88's avatar
    vf88
    1 year ago

    So I was doing some testing and this seems to work as I want. I created three measures:

    1. 

    Past_Month_Value =
    VAR LastDateWithData =
        LASTDATE('Data'[Date])
    VAR PastDateWithData =
        EDATE(LastDateWithData, -3)
    RETURN
        CALCULATE(
            SUM('Data'[Value]),
            FILTER(
                'FiscalDateTable',
                'FiscalDateTable'[Date] >= EOMONTH(PastDateWithData, -1) + 1 &&
                'FiscalDateTable'[Date] <= EOMONTH(PastDateWithData, 0)
            )
        )
     
    2. 
    Recent_Month_Value =
    VAR
    LastDateWithData =
        LASTDATE('Data'[Date])
    RETURN
        CALCULATE(
            SUM('Data'[Value]),
            FILTER(
                'FiscalDateTable',
                YEAR('FiscalDateTable'[Date]) = YEAR(LastDateWithData) &&
               MONTH('FiscalDateTable'[Date]) = MONTH(LastDateWithData)
            )
        )
     
    3. 
    QoQ change test =
    DIVIDE(
        [Recent_Month_Value] - [Past_Month_Value],
        [Past_Month_Value]
    )
     
    When i add this QoQ change as my line and select 2 any two consecutive quarters it appears to be giving me my expected result
     

     

4 Replies

  • johnt75's avatar
    johnt75
    Super User
    1 year ago

    Make sure that your date table is marked as a date table, and then you should be able to use

    QoQ % =
VAR CurrentValue =
    SUM ( 'Data'[Value] )
VAR PrevValue =
    CALCULATE (
        SUM ( 'Data'[Value] ),
        PARRALELPERIOD ( 'FiscalDateTable'[Date], -3, MONTH )
    )
VAR Result =
    DIVIDE ( CurrentValue - PrevValue, PrevValue )
RETURN
    Result
    • vf88's avatar
      vf88
      Regular Visitor
      1 year ago

      Thanks! this is getting me on the right track. If i make my visual a matrix its returning the correct values but in a bar/line graph it isnt. Any idea why? If i add it as a tooltip its correct. But I want a visual aspect of it on my visual aswell

       

  • johnt75's avatar
    johnt75
    Super User
    1 year ago

    I may be wrong but I think that the year-quarter is only being passed as a filter for the columns, not for the line chart as there is only value showing for each company on the line. As the QoQ needs to know the current quarter so that it can calculate the change, it won't work without year-quarter being passed in.

    I think that you would need to rethink the chart layout so that year-quarter is on the axis, and maybe the company in the legend. Either that or you'd need a separate chart entirely which shows QoQ% with year0-quarter and value as the axes and company as legend.

    • vf88's avatar
      vf88
      Regular Visitor
      1 year ago

      So I was doing some testing and this seems to work as I want. I created three measures:

      1. 

      Past_Month_Value =
      VAR LastDateWithData =
          LASTDATE('Data'[Date])
      VAR PastDateWithData =
          EDATE(LastDateWithData, -3)
      RETURN
          CALCULATE(
              SUM('Data'[Value]),
              FILTER(
                  'FiscalDateTable',
                  'FiscalDateTable'[Date] >= EOMONTH(PastDateWithData, -1) + 1 &&
                  'FiscalDateTable'[Date] <= EOMONTH(PastDateWithData, 0)
              )
          )
       
      2. 
      Recent_Month_Value =
      VAR
      LastDateWithData =
          LASTDATE('Data'[Date])
      RETURN
          CALCULATE(
              SUM('Data'[Value]),
              FILTER(
                  'FiscalDateTable',
                  YEAR('FiscalDateTable'[Date]) = YEAR(LastDateWithData) &&
                 MONTH('FiscalDateTable'[Date]) = MONTH(LastDateWithData)
              )
          )
       
      3. 
      QoQ change test =
      DIVIDE(
          [Recent_Month_Value] - [Past_Month_Value],
          [Past_Month_Value]
      )
       
      When i add this QoQ change as my line and select 2 any two consecutive quarters it appears to be giving me my expected result