Forum Discussion

rkxiii's avatar
rkxiii
Frequent Visitor
2 years ago

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

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, an...
Need Help
Show and Tell
Tutorial Requests
rkxiii's avatar
rkxiii
Frequent Visitor
2 years ago

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's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      rkxiii
      Frequent Visitor
      2 years ago

      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.