Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

% Difference between MIN and MAX date

Looking for help with % Change. A little background, I subtracted the ACV from the first year (MIN DATE) from the last year (MAX DATE) , so not a simple “let’s add up the sales” calculation. I ran into problems calculating the % difference.

 

When I plugged "MIN" into my first measure, the result comes up blank.  Not sure what I am missing here. The next two measures are working pristinely, so I am assuming I oversimplified the ending of my first measure. I have already calculated the difference between the two dates so I'm able to simplify part of the ACV% Change calc. 

Here are my three measures:

 

ACV % Change = DIVIDE([Change in ACV] , [MIN])

 

Change in ACV =
VAR __max = MAXx('Calendar Table','Calendar Table'[Year])
VAR __min = MINx('Calendar Table','Calendar Table'[Year])

RETURN

CALCULATE(SUM('Divya ACV'[Product Value]), FILTER(all('Calendar Table'), 'Calendar Table'[Year] = __max)) -CALCULATE(SUM('Divya ACV'[Product Value]), FILTER(all('Calendar Table'), ('Calendar Table'[Year]) =__min))

 

MIN =
VAR mind =
CALCULATE ( MIN ( 'Divya ACV'[Year] ), ALLSELECTED ( 'Divya ACV'[Year] ) )
RETURN
CALCULATE ( SELECTEDVALUE ( 'Divya ACV'[Product Value] ), 'Divya ACV'[Year] = mind )

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    @V-lianl-msft, @amitchandak

    Found a solution, sadly not sure how I got there, but here is what I did. I created new measures:

     

    First Date ACV = CALCULATE([ACV], FILTER( 'Divya ACV', 'Divya ACV'[Year] = [First Date]))
     
    where
     
    First Date = CALCULATE ( MIN ('Divya ACV'[Year]), ALLSELECTED( ( 'Divya ACV' )))
     
    and
     
    ACV % Change = DIVIDE('ACV'[Change in ACV], 'ACV'[First Date ACV])

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , what exactly, you are trying to achieve here.  The Min calc is wrong. What calculation you want min. What you want to display at the end

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      amitchandak

      Thank you. I am trying to calculate the % change between the maximum year and the minimum year. When I ran my MIN Calc through my table, I get the correct answer, so I think my ACV % Change DAX is wrong. Here is minimum calc in action:

       
       
       
       
       
       
      YearACVMINMAX
      12/31/2011 $1,397,633.70$6,553,524.60
      12/31/2012$1,397,633.70$1,397,633.70$6,553,524.60
      12/31/2013$2,472,531.60$1,397,633.70$6,553,524.60
      12/31/2014$4,010,372.20$1,397,633.70$6,553,524.60
      12/31/2015$5,295,672.70$1,397,633.70$6,553,524.60
      12/31/2016$6,553,524.60$1,397,633.70$6,553,524.60

       

      Ultimately I want it to calculate ($6,553,524.60 - $1,397,633.70)/$1,397,633.70

      • V-lianl-msft's avatar
        V-lianl-msft
        Community Support
        6 years ago

        Hi Anonymous ,

         

        Calculate "_max" and "_min" at "change in ACV" as you did before at "MIN"

         

        VAR mind =
        CALCULATE ( MIN ( 'Divya ACV'[Year] ), ALLSELECTED ( 'Divya ACV'[Year] ) )

         

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