dax

[krishna_1811]

how to show only quarters when QoQis  selected, months when MoM are selected and years when YOY are selected from the  slicer in a matrix

[Anonymous · Accepted solution]

Hi @krishna_1811 ,

To achieve this in Power BI, you can use a combination of DAX measures and a disconnected table for the slicer. Here's a step-by-step solution with sample data and the expected result:

1.Create a new, disconnected table for the slicer with the options "QoQ", "MoM", and "YoY". You can also create this table by entering data.

SlicerTable = DATATABLE(
    "Period", STRING,
    {
        {"QoQ"},
        {"MoM"},
        {"YoY"}
    }
)

2.Create a measure to calculate the values based on the selected period.

SaSalesAmountlesQoQ = 
VAR SelectedPeriod = SELECTEDVALUE(SlicerTable[Period], "QoQ")
VAR SalesQoQ =
IF(
    SelectedPeriod = "QoQ",
    CALCULATE(SUM(Sales[Amount]), DATESQTD(Sales[Date])),
    BLANK()
)

VAR SalesMoM = 
IF(
    SelectedPeriod = "MoM",
    CALCULATE(SUM(Sales[Amount]), DATESMTD(Sales[Date])),
    BLANK()
)

VAR  SalesYoY = 
IF(
    SelectedPeriod = "YoY",
    CALCULATE(SUM(Sales[Amount]), DATESYTD(Sales[Date])),
    BLANK()
)
RETURN

SalesQoQ + SalesMoM + SalesYoY

3.Create a Matrix Visual:

Add a matrix visual to your report.
Use the date field in the rows and the SalesAmount measure in the values.
Add the Period field from the SlicerTable to the slicer visual.

Sample Data
Let's assume you have a Sales table with the following data:

Date	Amount
2024-01-01	100
2024-02-01	150
2024-03-01	200
2024-04-01	250
2024-05-01	300
2024-06-01	350
2024-07-01	400
2024-08-01	450
2024-09-01	500
2024-10-01	550
2024-11-01	600
2024-12-01	650

Expected Result

When you select "QoQ" from the slicer, the matrix should display quarterly sales. When you select "MoM", it should display monthly sales, and when you select "YoY", it should display yearly sales.

Best Regards,
Stephen Tao

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

[rajendraongole1]

Hi @krishna_1811  - if you can share some sample data, that helps along with your expected output.

FOund sme resource Please find the same.

Dynamic Columns Based on Slicer Selection in Power BI

Dynamic hierarchy based on filter selection in #Matrix visual #powerbi

Please check.