Forum Discussion

lherbert501's avatar
lherbert501
Icon for Post Prodigy rankPost Prodigy
3 years ago
Solved

Dynamic Last MTD from Slicer

Hi,   I have my Last MTD query below which works okay for 1 month but I was wondering if there was a way to enable it to calculate the amount of months previous that is selected? E.G the image belo...
Need Help
  • amitchandak's avatar
    amitchandak
    3 years ago

    lherbert501 , Try like

     

    same period based on date range Last Period =
    var _max =maxx(ALLSELECTED('Date'),'Date'[date])
    var _min =Minx(ALLSELECTED('Date'),'Date'[date])
    var datediff1 = datediff(_min,_max,Month)+1
    var _maxX = eomonth(_max, -datediff1)
    var _minX = eomonth(_min, -datediff1 -1)+1
    return
    CALCULATE(sum(Sales[Net Sales]),filter(all('Date'),'Date'[date]<=_maxX && 'Date'[date]>=_minX))

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

lherbert501 , Try like

 

same period based on date range Last Period =
var _max =maxx(ALLSELECTED('Date'),'Date'[date])
var _min =Minx(ALLSELECTED('Date'),'Date'[date])
var datediff1 = datediff(_min,_max,Month)+1
var _maxX = eomonth(_max, -datediff1)
var _minX = eomonth(_min, -datediff1 -1)+1
return
CALCULATE(sum(Sales[Net Sales]),filter(all('Date'),'Date'[date]<=_maxX && 'Date'[date]>=_minX))

  • lherbert501's avatar
    lherbert501
    Icon for Post Prodigy rankPost Prodigy
    3 years ago

    Wow that's incredible amitchandak .

     

    Thankyou - You are a genius!