How to calculate QOQ based on quarter slicer selection without date table.

[binayjethwa]

Hi, 

I want to calculate the QOQ of my performance based on quarter selection.

Below are my columns. I have my performance measure which changes based on slicer selection.

how can i calculate QOQ based on slicer selection without any date field. 

If i select q4 , it should compare my q3 and q4 and give me % change.

Thanks,

Binay

[Anonymous]

Hi @binayjethwa ,

Please try below steps:

1. below is my test table

Table:

2. create a measure with below dax formula

Measure =
VAR ck_qt =
    SELECTEDVALUE ( 'Table'[Quarter] )
VAR _num =
    INT ( RIGHT ( ck_qt, 1 ) )
VAR tmp =
    FILTER ( ALL ( 'Table' ), INT ( RIGHT ( [Quarter], 1 ) ) = _num - 1 )
VAR tmp1 =
    FILTER ( ALL ( 'Table' ), INT ( RIGHT ( [Quarter], 1 ) ) = _num )
VAR base =
    SUMX ( tmp, [Performance] )
VAR now =
    SUMX ( tmp1, [Performance] )
VAR _val = now - base
VAR _val1 =
    FORMAT ( DIVIDE ( _val, base ), "Percent" )
RETURN
    _val1

3. add a slicer with "Table[Quarter]" column, add a card visual with measure

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[binayjethwa]

Hi @Anonymous  the above is working as expected after i change some formula based on my condition , but how to make it work with the combination of Year and Quarter as my requirement is based on Year and quarter selection.

Thanks,

Binay

[Anonymous · Accepted solution]

Hi @binayjethwa ,

1.below is my test table

Table:

2. create a measure with below dax formula

Measure =
VAR ck_qt =
    SELECTEDVALUE ( 'Table'[Year Quanter] )
VAR q_num =
    INT ( RIGHT ( ck_qt, 1 ) )
VAR _year =
    INT ( LEFT ( ck_qt, 4 ) )
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        INT ( RIGHT ( [Year Quanter], 1 ) ) = q_num - 1
            && YEAR ( [Date] ) = _year
    )
VAR tmp1 =
    FILTER (
        ALL ( 'Table' ),
        INT ( RIGHT ( [Year Quanter], 1 ) ) = q_num
            && YEAR ( [Date] ) = _year
    )
VAR tmp2 =
    FILTER (
        ALL ( 'Table' ),
        INT ( RIGHT ( [Year Quanter], 1 ) ) = 4
            && YEAR ( [Date] ) = _year - 1
    )
VAR base =
    SUMX ( tmp, [Performance] )
VAR _base =
    SUMX ( tmp2, [Performance] )
VAR now =
    SUMX ( tmp1, [Performance] )
VAR _val = now - base
VAR _val2 = now - _base
VAR _val1 =
    FORMAT ( DIVIDE ( _val, base ), "Percent" )
VAR _val3 =
    FORMAT ( DIVIDE ( _val2, _base ), "Percent" )
RETURN
    IF ( q_num = 1, _val3, _val1 )

3.add a slicer with "Table[Year Quanter]" column, add a card visual with measure

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[binayjethwa]

@Anonymous  How will it work if quarter and Year slicers are different and not one single slicer