Percentage Difference based on selected slicer (per week, and per month)

[rkxiii]

Good day!

I would like to ask for your help regarding Percentage Difference Data based on selected slicer (per week and per month).

I am using 3 different slicers for my data (Month, Week, and Date) and this is the formula I am using for the Percentage Difference for Date/Day (see formula below)

% Diff Day =

Var _Prevdate=CALCULATE(MAX('Input Data'[Date]),FILTER(ALLEXCEPT('Input Data','Input Data'[Model],'Input Data'[Line ]),[Date]<SELECTEDVALUE('Input Data'[Date],'Input Data'[Week])))

Var _PrevYield=CALCULATE([Yield],FILTER(ALLEXCEPT('Input Data','Input Data'[Model],'Input Data'[Line ]),[Date]=_Prevdate))

RETURN

IF(_PrevYield=Blank(),Blank(),[Yield]-_PrevYield)

Is it possible to include IF OR Condition for this formula If I only selects for Month or Week Slicer? How to input?

For Example: If I selected for the Month Slicer only, it will calculate for the percentage difference from the previous month. Same scenario when I select in the Week Slicer but if I selected for all 3 Slicers, For Example: Month: August, Week: 35, Date: Aug 26, the Percentage Difference will be from the previous day (Aug 25)

Thank you!

[rkxiii]

Here's the formula I am using for Percentage Difference per Day

% Diff Day =

Var _Prevdate=CALCULATE(MAX('Input Data'[Date]),FILTER(ALLEXCEPT('Input Data','Input Data'[Model],'Input Data'[Line ]),[Date]<SELECTEDVALUE('Input Data'[Date],'Input Data'[Week])))

Var _PrevYield=CALCULATE([Yield],FILTER(ALLEXCEPT('Input Data','Input Data'[Model],'Input Data'[Line ]),[Date]=_Prevdate))

RETURN

IF(_PrevYield=Blank(),Blank(),[Yield]-_PrevYield)

Here's the formula I am using for Percentage Difference per Week

Week Diff =

Var _Prevdate=CALCULATE(MAX('Input Data'[Week]),FILTER(ALLEXCEPT('Input Data','Input Data'[Model],'Input Data'[Line ]),[Week]<SELECTEDVALUE('Input Data'[Week],'Input Data'[Date])))

Var _PrevYield=CALCULATE([Yield],FILTER(ALLEXCEPT('Input Data','Input Data'[Model],'Input Data'[Line ]),[Week]=_Prevdate))

RETURN

IF(_PrevYield=Blank(),Blank(),[Yield]-_PrevYield)

I need help on how to combine these formulas with IF OR Statement that when I selected for Week slicer, it will show the % difference from the previous week and same scenario for day slicer. Thank you!

[Anonymous]

Hi, @rkxiii 

Can you provide some of the example data? And what you expect the output to be.

Best Regards,

Community Support Team _Charlotte

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

[rkxiii]

Hi, Thank you for the response.

Is it possible for you to combine those 2 formulas above? 

Shravan133's formula gives wrong calculations for week and month percent difference.

Here's my expected output as I stated on my post: "If I selected for the Month Slicer only, it will calculate for the percentage difference from the previous month. Same scenario when I select in the Week Slicer but if I selected for all 3 Slicers, For Example: Month: August, Week: 35, Date: Aug 26, the Percentage Difference will be from the previous day (Aug 25)"

Thank you very much. 

[Shravan133]

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.

[rkxiii]

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