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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi amitchandak ,

       

      Whats the best way to send/share the PBIX?

       

      Thanks

       

      Liam

      • v-xuding-msft's avatar
        v-xuding-msft
        Community Support
        6 years ago

        Hi Anonymous ,

        You could upload your dummy pbix file into OneDrive for Business and post the link here. Please do mask sensitive data before uploading.

         

        Best Regards,

        Xue Ding

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