Need Urgent Help on DAX

[heetu24]

I have 2 slicer one Slicer is for year. Where 3 filters TY, YA, 2YA. And another slicer is Period. MAT, QTR and YTD. Each Period  MAT, QTR & YTD represent TY, YA and 2YA. I have  created 6 measure   TY VS YA Growth %  & YA VS 2YA Growth %

1) MAT TY VS YA Growth %,

2)YTD TY VS YA Growth %

3) QTR TY VS YA Growth %

Rest 3 for same as above Period  YA  VS 2YA Growth %

My Objective is if User Select TY and MAT In the table it shows  " MAT TY VS YA Growth %,"

second is when user select YA and MAT then " MAT YA VS 2YA Growth %," dynamically filter  can work  for same  other Period  alos how to achieve this objective in Power BI?

Here is the DAX I have created .  For Example  

MAT TY Vs YA Growth %= Divide(calculate(sum(‘FactTable’[SalesValue]),
‘FactTable’[Period] =”TY”, ‘FactTable’[TimeGroup] = “MAT”) - (calculate(sum(‘FactTable’[SalesValue]),
‘FactTable’[Period] =”YA”, ‘FactTable’[TimeGroup] = “MAT”),
(calculate(sum(‘FactTable’[SalesValue]),
‘FactTable’[Period] =”YA”, ‘FactTable’[TimeGroup] = “MAT”))

Above DAX is Growth %=YA Sales(TY Sales−YA Sales)​/ YA Sales

TY = This Year

YA =Year ago

[Anonymous · Accepted solution]

Hi @heetu24 ,

You can modify your formula like below:

MAT_TY_VS_YA_Growth = DIVIDE(
    CALCULATE(SUM('FactTable'[SalesValue]), 'FactTable'[Period] = "TY", 'FactTable'[TimeGroup] = "MAT") - 
    CALCULATE(SUM('FactTable'[SalesValue]), 'FactTable'[Period] = "YA", 'FactTable'[TimeGroup] = "MAT"),
    CALCULATE(SUM('FactTable'[SalesValue]), 'FactTable'[Period] = "YA", 'FactTable'[TimeGroup] = "MAT")
)

MAT_YA_VS_2YA_Growth = DIVIDE(
    CALCULATE(SUM('FactTable'[SalesValue]), 'FactTable'[Period] = "YA", 'FactTable'[TimeGroup] = "MAT") - 
    CALCULATE(SUM('FactTable'[SalesValue]), 'FactTable'[Period] = "2YA", 'FactTable'[TimeGroup] = "MAT"),
    CALCULATE(SUM('FactTable'[SalesValue]), 'FactTable'[Period] = "2YA", 'FactTable'[TimeGroup] = "MAT")
)

DynamicGrowth = SWITCH(
    TRUE(),
    [SelectedYear] = "TY" && [SelectedPeriod] = "MAT", [MAT_TY_VS_YA_Growth],
    [SelectedYear] = "YA" && [SelectedPeriod] = "MAT", [MAT_YA_VS_2YA_Growth],
    BLANK()
)

Best Regards,
Adamk Kong

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

[ahadkarimi]

Hi @heetu24, create these measures and if you encounter any issues, let me know.

SelectedYear:

SelectedYear = SELECTEDVALUE('YearSlicer'[Year])

SelectedPeriod:

SelectedPeriod = SELECTEDVALUE('PeriodSlicer'[Period])

MAT TY Vs YA Growth:

MAT TY Vs YA Growth % = 
DIVIDE(
    CALCULATE(SUM('FactTable'[SalesValue]), 
        'FactTable'[Period] = "TY", 
        'FactTable'[TimeGroup] = "MAT"
    ) 
    - 
    CALCULATE(SUM('FactTable'[SalesValue]), 
        'FactTable'[Period] = "YA", 
        'FactTable'[TimeGroup] = "MAT"
    ), 
    CALCULATE(SUM('FactTable'[SalesValue]), 
        'FactTable'[Period] = "YA", 
        'FactTable'[TimeGroup] = "MAT"))

MAT YA Vs 2YA Growth:

MAT YA Vs 2YA Growth % = 
DIVIDE(
    CALCULATE(SUM('FactTable'[SalesValue]), 
        'FactTable'[Period] = "YA", 
        'FactTable'[TimeGroup] = "MAT"
    ) 
    - 
    CALCULATE(SUM('FactTable'[SalesValue]), 
        'FactTable'[Period] = "2YA", 
        'FactTable'[TimeGroup] = "MAT"
    ), 
    CALCULATE(SUM('FactTable'[SalesValue]), 
        'FactTable'[Period] = "2YA", 
        'FactTable'[TimeGroup] = "MAT"))

Dynamic Growth:

Dynamic Growth % = 
SWITCH(
    TRUE(),
    'SelectedYear' = "TY" && 'SelectedPeriod' = "MAT", [MAT TY Vs YA Growth %],
    'SelectedYear' = "YA" && 'SelectedPeriod' = "MAT", [MAT YA Vs 2YA Growth %],
    'SelectedYear' = "TY" && 'SelectedPeriod' = "QTR", [QTR TY Vs YA Growth %], 
    'SelectedYear' = "YA" && 'SelectedPeriod' = "QTR", [QTR YA Vs 2YA Growth %], 
    'SelectedYear' = "TY" && 'SelectedPeriod' = "YTD", [YTD TY Vs YA Growth %], 
    'SelectedYear' = "YA" && 'SelectedPeriod' = "YTD", [YTD YA Vs 2YA Growth %], 
    BLANK()
)

[heetu24]

Thank you for you help. I tried but in the table it is not working.

One more point I want that growth I want to show by comparing two dimension column for Example in Matrix visual In rows it is product and In column it is Category and in value these measure but as a result it is coming blank