Dynamic Columns based on Slicer selection

[Sekhar1]

Hello,

I have below sample data with 4 Columns. 

Country	Account	Year	Value
AUS	EBITDA	2022	13
AUS	EBITDA	2023	10
AUS	EBITDA	2018	98
AUS	EBITDA	2021	120
AUS	EBITDA	2020	700
AUS	EBITDA	2019	10
AUS	Sales	2020	13
AUS	Sales	2021	10
AUS	Sales	2023	39
AUS	Sales	2019	120
AUS	Sales	2018	145
AUS	Sales	2022	28
AUS	ITDA	2023	10
AUS	ITDA	2022	231
AUS	ITDA	2021	39

When User selects 2020 from Slicer, Clustered Chart should display below Columns only :-

1. EBITDA for 2020 and last 2 years (2020, 2019 and 2018).
2. Sales for 2020 only
3. ITDA for next 2 years (i.e. 2021 and 2022)

Chart shall be displayed as per below:-

Can you please advise on how to get the above result?

[v-rzhou-msft · Accepted solution]

Hi @Sekhar1 ,

Here I suggest you to try this code to create a measure.

Measure = 
VAR _SELECTYEAR = SELECTEDVALUE('Table 2'[Year])
VAR _VALUE = 
IF(
    ISFILTERED('Table 2'[Year]),
    SWITCH(
        MAX('Table'[Account]),
        "EBITDA",
        CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Year]>=_SELECTYEAR-2&&'Table'[Year]<=_SELECTYEAR)),
        "Sales",
        CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Year]=_SELECTYEAR)),
        "ITDA",
        CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Year]>_SELECTYEAR&&'Table'[Year]<=_SELECTYEAR+2))
        )
    )
RETURN
_VALUE

Result is as below.

Best Regards,
Rico Zhou

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