Forum Discussion
Solved
Divide based on multiple conditions
Hi all I struggle to do a YoY comparison based on some criterias I only need the % comparison to happen between month 1 and 12 as the months are rolling- Month 4 and 8 are irrelevant. I have a d...
With this model, containing dimension tables for country and month,
and this measure (with a simple SUM base measure for the ratio):
YoY % Change measure = VAR _1 = CALCULATE ( [Sum Ratio], 'Dim Month'[dMonth] = 1, ALLEXCEPT ( FactTable, 'Dim COuntry'[dCountry] ) ) VAR _12 = CALCULATE ( [Sum Ratio], 'Dim Month'[dMonth] = 12, ALLEXCEPT ( FactTable, 'Dim COuntry'[dCountry] ) ) RETURN DIVIDE ( _12, _1 ) - 1I've attached the sample PBIX file
With this model, containing dimension tables for country and month,
and this measure (with a simple SUM base measure for the ratio):
YoY % Change measure =
VAR _1 =
CALCULATE (
[Sum Ratio],
'Dim Month'[dMonth] = 1,
ALLEXCEPT ( FactTable, 'Dim COuntry'[dCountry] )
)
VAR _12 =
CALCULATE (
[Sum Ratio],
'Dim Month'[dMonth] = 12,
ALLEXCEPT ( FactTable, 'Dim COuntry'[dCountry] )
)
RETURN
DIVIDE ( _12, _1 ) - 1
I've attached the sample PBIX file
That was a very clever solution! Thank you. It works.