Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

$ Difference between MIN and MAX dates

Hello, I am trying to create a card showing the difference in ACV between the MAX date and the MIN date. This is tied to a date filter on a bar graph, so when the user switches years on the graph, th...
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Anonymous 

    Try like

    measure =
var _max = maxx('Date','Date'[Date])
var _min = Minx('Date','Date'[Date])
return
CALCULATE(SUM(Table[ACV]), FILTER(all('Date'), 'Date'[Date] =_max)) -CALCULATE(SUM(Table[ACV]), FILTER(all('Date'), 'Date'[Date] =_min))
/////////////////////////////////Or

measure =
var _max = maxx('Date','Date'[Date])
var _min = Minx('Date','Date'[Date])
return
CALCULATE(SUM(Table[ACV]), FILTER(all('Date'), year('Date'[Date]) =year(_max))) -CALCULATE(SUM(Table[ACV]), FILTER(all('Date'), year('Date'[Date]) =year(_min)))
v-lili6-msft's avatar
v-lili6-msft
Icon for Community Support rankCommunity Support
6 years ago

hi  Anonymous 

You could try the logic as below:

Measure = 
var _minyear=MIN('Table'[Year]) 
var _maxyear=MAX('Table'[Year])
return
CALCULATE(SUM('Table'[ACV]),FILTER(ALLEXCEPT('Table','Table'[Product Category]),'Table'[Year]=_maxyear))-CALCULATE(SUM('Table'[ACV]),FILTER(ALLEXCEPT('Table','Table'[Product Category]),'Table'[Year]=_minyear))
Measure 2 = 
var _minyear=MIN('Table'[Year]) 
var _maxyear=MAX('Table'[Year])
return
CALCULATE(SUM('Table'[ACV]),FILTER('Table','Table'[Year]=_maxyear))-CALCULATE(SUM('Table'[ACV]),FILTER('Table','Table'[Year]=_minyear))

 Result:

Regards,

Lin