Forum Discussion

heetu24's avatar
heetu24
Icon for Helper I rankHelper I
2 years ago
Solved

Need Urgent Help on DAX

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   ...
"Need Help"
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
ahadkarimi
Icon for Solution Specialist rankSolution Specialist
2 years ago

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's avatar
heetu24
Icon for Helper I rankHelper I
2 years ago

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