Forum Discussion

orana's avatar
orana
Icon for Helper I rankHelper I
4 years ago
Solved

User determined variable in calculation

I'm looking to see if there is a way to take the difference between two variables  1. average sales in 2019 and 2020 for product B-A 2. sales in "x" year for product M-A, where "x" would be determine...
Need Help
  • PabloDeheza's avatar
    PabloDeheza
    4 years ago

    Try changing the first calculate to this:

    CALCULATE(
    SUM( 'PL Portfolio'[Trace Units] ),
    'PL Portfolio'[Fiscal Year] <= 2020,
    ALL( 'PL Portfolio'[Fiscal Year] ),
    'PL Portfolio'[B & M] = "B - A"
)

     

orana's avatar
orana
Icon for Helper I rankHelper I
4 years ago

Hey Pablo ,

 

I revised my post with the measure that I currently have. Let me know if this helps

 

Best,

Oscar

PabloDeheza's avatar
PabloDeheza
Icon for Solution Sage rankSolution Sage
4 years ago

Try changing the first calculate to this:

CALCULATE(
    SUM( 'PL Portfolio'[Trace Units] ),
    'PL Portfolio'[Fiscal Year] <= 2020,
    ALL( 'PL Portfolio'[Fiscal Year] ),
    'PL Portfolio'[B & M] = "B - A"
)

 

  • orana's avatar
    orana
    Icon for Helper I rankHelper I
    4 years ago

    The measure is not working. If I use my slicer and click 2021, the measure returns blank. 

    • v-kkf-msft's avatar
      v-kkf-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      Hi orana ,

       

      I was able to return the correct result using the formula from PabloDeheza . Could you please share your example data? That will help us solve the problem faster.

       

       

      Best Regards,
      Winniz

      • orana's avatar
        orana
        Icon for Helper I rankHelper I
        4 years ago

        Hey,

         

        I just got it to work. I was using another Fiscal Year for the slicer, which is what was driving the "blank".