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. Eve...
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
     

     

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