Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

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

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.
r12 graf.PNG

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!


2 REPLIES 2
stevedep
Memorable Member
Memorable Member

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
Resolver III
Resolver III

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
                 )
      )

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors