Forum Discussion

newgirl's avatar
newgirl
Post Patron
4 years ago
Solved

MTD compared to Dynamic Month

Hi! In the PBI report I'm making, I have a table in which it compares month-to-date (MTD) volume versus other columns such as: full previous month (PM), previous, year (PY), trending, and another mo...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    newgirl , You might need to use an independent date slicer for that select a range there and use that in an measure

     

    a new measure. Assume date is joined table and date one is independent table from where you will select month

     

    var _max = maxx(allselected('Date1'), 'Date1'[Date])
    var _min = maxx(allselected('Date1'), 'Date1'[Date])
    return
    calculate(sum(Table[Value]), filter(all('date'), 'Date'[Date] >=_min && 'Date'[Date] <=_max ))

     

    Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

     

    How to use two Date/Period slicers :https://www.youtube.com/watch?v=WSeZr_-MiTg

amitchandak's avatar
amitchandak
Super User
4 years ago

newgirl , You might need to use an independent date slicer for that select a range there and use that in an measure

 

a new measure. Assume date is joined table and date one is independent table from where you will select month

 

var _max = maxx(allselected('Date1'), 'Date1'[Date])
var _min = maxx(allselected('Date1'), 'Date1'[Date])
return
calculate(sum(Table[Value]), filter(all('date'), 'Date'[Date] >=_min && 'Date'[Date] <=_max ))

 

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

 

How to use two Date/Period slicers :https://www.youtube.com/watch?v=WSeZr_-MiTg

newgirl's avatar
newgirl
Post Patron
4 years ago

Thanks amitchandak ! Your suggestion worked.

 

For others' reference, below is the final measure I used:

DiffMonth = 
var _max = MAXX(ALLSELECTED('Calendar2'),'Calendar2'[Date])
var _min = MINX(ALLSELECTED('Calendar2'),'Calendar2'[Date])
return
CALCULATE(SUM('Table'[Volume]),FILTER(ALL('Calendar'[Date]),'Calendar'[Date]>= _min && 'Calendar'[Date] <= _max))