Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Previous month calculation

Hi,   I'm looking to add a measure to a filter on to my table.    I need to get the data to display the previous month and up to 10th of the next.   E.g. if my report runs on the 1st April I ne...
Need Help
amitchandak's avatar
amitchandak
Super User
6 years ago

Anonymous 

Try Like

last Month =
var _max1 = maxx('Sales','Sales'[Date])
var _max = date(year(_max1),month(_max1)-1,10)
var _min = date(year(_max),month(_max)-1,1)

return
CALCULATE(SUM('Sales'[Sales]), FILTER(all('Date'), 'Date'[Date]<=_max && 'Date'[Date]>=_min))
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi amitchandak ,

     

    Would that measure be able to filter the below table?

     

    With the example below it would only have february and the first 10 days of March?

     

     

    thanks

     

    Liam

    • amitchandak's avatar
      amitchandak
      Super User
      6 years ago

      Anonymous 

      Try Like

      last Month =
var _maxP = maxx('Period','Period'[snapshot Period])
var _max1 = maxx(filter('Period','Period'[snapshot Period]=_maxP),'Period'[Date])
var _max = date(year(_max1),month(_max1)-1,10)
var _min = date(year(_max),month(_max)-1,1)

return
CALCULATE(SUM('Sales'[Sales]), FILTER(all('Date'), 'Date'[Date]<=_max && 'Date'[Date]>=_min))

       

      Can you share sample data and sample output.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi amitchandak ,

         

        I basically want it to show february and up to  march 10 when you select  2020-03 in the snapshot period.

         

        How can I attach the sample file I have?

         

        Thanks in advance

         

        Liam