Forum Discussion
Percentage Difference based on selected slicer (per week, and per month)
try this:
% Diff =
VAR SelectedDate = SELECTEDVALUE('Input Data'[Date])
VAR SelectedWeek = SELECTEDVALUE('Input Data'[Week])
VAR SelectedMonth = SELECTEDVALUE('Input Data'[Month])
VAR _PrevDate =
IF(
NOT(ISBLANK(SelectedDate)),
CALCULATE(
MAX('Input Data'[Date]),
FILTER(
ALLEXCEPT('Input Data', 'Input Data'[Model], 'Input Data'[Line]),
'Input Data'[Date] < SelectedDate
)
),
IF(
NOT(ISBLANK(SelectedWeek)),
CALCULATE(
MAX('Input Data'[Date]),
FILTER(
ALLEXCEPT('Input Data', 'Input Data'[Model], 'Input Data'[Line]),
'Input Data'[Week] < SelectedWeek && 'Input Data'[Month] = SelectedMonth
)
),
CALCULATE(
MAX('Input Data'[Date]),
FILTER(
ALLEXCEPT('Input Data', 'Input Data'[Model], 'Input Data'[Line]),
'Input Data'[Month] < SelectedMonth
)
)
)
)
VAR _PrevYield =
CALCULATE(
[Yield],
FILTER(
ALLEXCEPT('Input Data', 'Input Data'[Model], 'Input Data'[Line]),
'Input Data'[Date] = _PrevDate
)
)
RETURN
IF(
ISBLANK(_PrevYield),
BLANK(),
[Yield] - _PrevYield
)
test the dax with different combinations of slicers and modify the formula accordingly.
Hi, Thank you for your response. The only problem for this formula is that the accumulated weekly and monthly percentage difference is not accurate. I think it is not based on the previous week/month when I select for week/month slicer