Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Power BI QoQ Calculation DAX

Hi there,

I'm calculating QoQ Imp and QoQ %Eng in the below data table which is grouped by with the help of power query by adding "Index starting from 0" and "Index.1 starting from 1".  

Table A

QtrFilterImpEng% Eng IndexIndex.1QoQ ImpQoQ %Eng
Q1 2021ABC15241066.96%01  
Q2 2021ABC20141879.29%12  
Q3 2021ABC206523911.57%23  
Q4 2021ABC228734515.09%34  
Q1 2021XYZ254343216.99%45  
Q2 2021XYZ342264318.79%56  
Q3 2021XYZ359876421.23%67  
Q4 2021XYZ367385423.25%78  
Q1 2021AUS17431206.88%89  
Q2 2021AUS197621110.68%910  
Q3 2021AUS203228714.12%1011  
Q4 2021AUS235539916.94%1112  
Q1 2021IND321143213.45%1213  
Q2 2021IND364352114.30%1314  
Q3 2021IND379860715.98%1415  
Q4 2021IND389872118.50%1516  

 

I have a "filter" column in the Filters pane this visual.

Please help me in calculating QoQ Imp and QoQ %Eng in the above Table A. The expected result/output should look like this below table:-

QtrFilterImpQoQ ImpEng_Sum% Eng QoQ %Eng
Q1 2021IND3211-43213.45%-
Q2 2021IND364313%52114.30%6.30%
Q3 2021IND37984%60715.98%11.75%
Q4 2021IND38983%72118.50%15.73%

I'm new to Power BI, any help would be much appreciated.

 

Thanks in Advance,

AK

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi, Anonymous 

    You can try calculated columns as below:

    QoQ Imp = 
VAR lastImp =
    LOOKUPVALUE (
        'Table A'[Imp],
        'Table A'[Filter], 'Table A'[Filter],
        'Table A'[Index], 'Table A'[Index] - 1
    )
RETURN
    IF ( ISBLANK ( lastImp ), BLANK (), ( 'Table A'[Imp] - lastImp ) / lastImp )
    QoQ %Eng = 
VAR last_Engpercent =
    LOOKUPVALUE (
        'Table A'[% Eng],
        'Table A'[Filter], 'Table A'[Filter],
        'Table A'[Index], 'Table A'[Index] - 1
    )
RETURN
    IF (
        ISBLANK ( last_Engpercent ),
        BLANK (),
        ( 'Table A'[% Eng] - last_Engpercent ) / last_Engpercent
    )

    Please check my sample file for more details.

    Best Regards,
    Community Support Team _ Eason

     

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    You may want to add a Calendar table to your data model, and then add a stand-in date column to your fact table (for example first day of quarter).

    That removes the need for your index columns and allows you to use the standard DAX time intelligence functions.

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Anonymous 

    You can try calculated columns as below:

    QoQ Imp = 
VAR lastImp =
    LOOKUPVALUE (
        'Table A'[Imp],
        'Table A'[Filter], 'Table A'[Filter],
        'Table A'[Index], 'Table A'[Index] - 1
    )
RETURN
    IF ( ISBLANK ( lastImp ), BLANK (), ( 'Table A'[Imp] - lastImp ) / lastImp )
    QoQ %Eng = 
VAR last_Engpercent =
    LOOKUPVALUE (
        'Table A'[% Eng],
        'Table A'[Filter], 'Table A'[Filter],
        'Table A'[Index], 'Table A'[Index] - 1
    )
RETURN
    IF (
        ISBLANK ( last_Engpercent ),
        BLANK (),
        ( 'Table A'[% Eng] - last_Engpercent ) / last_Engpercent
    )

    Please check my sample file for more details.

    Best Regards,
    Community Support Team _ Eason