Show rolling 12 months sale to last calendar month (january)

[Anonymous]

Hi, I have this measure for 12 months rolling sales:

CALCULATE(

SUM('Sales'[Sales_Amount_Actual]),

DATESBETWEEN(Date[Date],

NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE(Date[Date]))),

LASTDATE(Date[Date])

)

The problem is that it includes the current month (february), and I dont want the visual to show that.


How can I put in some kind of lastcalendarmonth function, so that it stops at 2021/Jan, and when february is over 2021/Feb will be included in the graph, and so on..?

Appriciate any help!

[stevedep]

I suggest to look at the endofmonth function, so go one month back and then endofmonth as your boundary.

https://docs.microsoft.com/nl-nl/dax/endofmonth-function-dax 

Hope it helps. Kind regards, Steve. 

[rfigtree]

hi havnt tried but maybe something like this.

might get funky if your calendar is not complete?

var minDate=EOMONTH(MAX('Calendar'[Date]),-13)
var fromDate=DATE(YEAR(minDate),MONTH(minDate),1)
var toDate=EOMONTH(MAX('Calendar'[Date]),-1)
return	
      CALCULATE(SUM(Table2[value]),
                 FILTER(ALL('Calendar'),  
                       'Calendar'[Date]>=fromDate &&
                       'Calendar'[Date]<=toDate
                 )
      )