Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

YoY + QoQ bar chart

Hi,

 

I am very new to Power BI and need a helping hand! I am trying to make a bar chart showing e.g. 2019 Q3, 2020 Q2 and 2020 Q3 (in that order) to show growth both QoQ and YoY. I have as of now made a Quarterly filter enabling me to manually select the needed quarters.

 

My problem arises as I would like my filter to be interactive in the way that when choosing the current quarter the other quarters would update automatically. 

Need Help
Tips and Tricks

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    Here is my sample data.

    DateValue

    1/1/20191
    2/2/20195
    3/3/20195
    4/4/20197
    5/5/20198
    5/6/20199
    6/6/20194
    7/7/20191
    8/1/20191
    8/2/20195
    9/1/20194
    9/2/20196
    10/1/20198
    10/2/20197
    11/15/20194
    11/16/20191
    1/5/20202
    2/5/20203
    3/5/20206
    4/25/20204
    5/15/20204
    5/25/20207
    6/25/20208
    6/27/20209
    7/1/20201
    7/2/20205
    8/8/20205
    9/9/20201
    10/10/20204

     

    1.Create a calendar table. A relationship is established between the main table and the calendar table.

     

    Calendar = ADDCOLUMNS(CALENDAR(DATE(2019,1,1),DATE(2020,12,10)),"YearQuarter",YEAR([Date])&"-"&QUARTER([Date]))

     

     

    2.Create a measure. Set the Measure value as a percentage output.

    Measure =
VAR _thisquarter =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( 'Calendar', [YearQuarter] = SELECTEDVALUE ( 'Calendar'[YearQuarter] ) )
    )
VAR _lastquarter =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Calendar' ),
            [YearQuarter]
                = LEFT ( SELECTEDVALUE ( 'Calendar'[YearQuarter] ), 4 ) - 1 & "-"
                    & RIGHT ( SELECTEDVALUE ( 'Calendar'[YearQuarter] ), 1 )
        )
    )
VAR tt =
    LEFT ( SELECTEDVALUE ( 'Calendar'[YearQuarter] ), 4 ) - 1 & "-"
        & RIGHT ( SELECTEDVALUE ( 'Calendar'[YearQuarter] ), 1 )
RETURN
    DIVIDE ( _thisquarter - _lastquarter, _lastquarter )

     

    3.Create a slicer and a bar chart. The result is as follows

     

     

    You can check more details from here.

     

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

    Could you tell me if your problem has been solved?
    If it is, kindly Accept it as the solution. More people will benefit from it.
    Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.


    Best Regards,
    Stephen Tao