Forum Discussion

jogenpatel's avatar
jogenpatel
Frequent Visitor
7 years ago
Solved

QoQ calculation without date table

Hi,

 

My sample table is as below:

 

TicketsFYQtr
A2FY19Q1
A4FY19Q2
A1FY19Q3
A5FY19Q3
A33FY19Q2
A412FY19Q4
A153FY19Q1
A512FY19Q1

 

 

I've summarize the data by FY and Qtr with count(Tickets) as values. I want to calculate QoQ based on Qtr column. I do not have any date table.  Want to find the difference between current qtr Vs previous qtr.

 

thanks

Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    Hi jogenpatel ,

     

    Looking at your data and considering you don't have a date table you can try something similar to what is below:

     

    QoQ =
VAR CurrentQuarter =
    MAX ( Quarter_Calculation[Qtr] )
RETURN
    IF (
        RIGHT ( CurrentQuarter; 1 ) - 1 = 0;
        COUNT ( Quarter_Calculation[Tickets] );
        COUNT ( Quarter_Calculation[Tickets] )
            - CALCULATE (
                COUNT ( Quarter_Calculation[Tickets] );
                FILTER (
                    ALL ( Quarter_Calculation[Qtr] );
                    Quarter_Calculation[Qtr]
                        = CONCATENATE ( "Q"; RIGHT ( CurrentQuarter; 1 ) - 1 )
                )
            )
    )

    Be aware that this is not making calculation with change of year, only quarter.

     

    Regards.

    MFelix

2 Replies

  • MFelix's avatar
    MFelix
    Super User
    7 years ago

    Hi jogenpatel ,

     

    Looking at your data and considering you don't have a date table you can try something similar to what is below:

     

    QoQ =
VAR CurrentQuarter =
    MAX ( Quarter_Calculation[Qtr] )
RETURN
    IF (
        RIGHT ( CurrentQuarter; 1 ) - 1 = 0;
        COUNT ( Quarter_Calculation[Tickets] );
        COUNT ( Quarter_Calculation[Tickets] )
            - CALCULATE (
                COUNT ( Quarter_Calculation[Tickets] );
                FILTER (
                    ALL ( Quarter_Calculation[Qtr] );
                    Quarter_Calculation[Qtr]
                        = CONCATENATE ( "Q"; RIGHT ( CurrentQuarter; 1 ) - 1 )
                )
            )
    )

    Be aware that this is not making calculation with change of year, only quarter.

     

    Regards.

    MFelix