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

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