Forum Discussion

ssh529's avatar
ssh529
Regular Visitor
2 years ago

Dynamically subtracting one option from another within the same variable

Hi everyone,

 

I have data for 8 quarters (but the format of the quarter variable in the data is text, not a date).

 

I'm looking to make a dynamic calculation depending on what quarter I pick in the filter, that it will compare this quarters score to the previous quarters score. 

 

For example, here I have clicked Q2 2023 in my filter. The score for Q2 2023 is 11. I now want a KPI that would show +5, since the score for Q1 2023 was 6. 

If I chose Q3 2023 in the filter, I would want the KPI to show -3, since Q2 2023 is 11.

 

I just always want the quarter selected to compare to the quarter before it. But since the quarters are listed as text options within one Quarter variable, I'm not sure how to acheive. 

(This would be a table calculation in Tableau but not sure how to translate here).

 

Thanks! 

Need Help

1 Reply

  • marcelsmaglhaes's avatar
    marcelsmaglhaes
    Super User
    2 years ago

    Hey ssh529 ,

    You can try something like the Comparioson Measure bellow. It's a simple starting point that can be refined to meet your specific requirements. Just make sure to replace 'YourTable' with your actual table name and 'YourValueColumn' with the name of the column containing the values you want to compare.

    Comparison Measure =
    VAR CurrentQuarter = SELECTEDVALUE('YourTable'[Quarter]) // Replace 'YourTable' with your table name
    VAR PreviousQuarter =
    IF(
    CurrentQuarter = "Q1",
    "Q4",
    CONCATENATE("Q", TEXT(VALUE(RIGHT(CurrentQuarter, 1)) - 1))
    )
    RETURN
    CALCULATE(
    SUM('YourTable'[YourValueColumn]), // Replace 'YourValueColumn' with your value column name
    'YourTable'[Quarter] = PreviousQuarter
    )