Quarter over Quarter Line graph

[vf88]

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"

            )

        )

)

[johnt75]

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 · Accepted solution]

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]

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]

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