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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Andrea_Jess
Helper III
Helper III

Returning figures for current month - 1

Hi All, 

 

i have a dataset like the below and i need to show (each month), what the final sales figures were for the prior month. I.e. we are in October, so i would need to show Sept sales number ($98,500). 

 

Month-YearSales
Aug-21$100,000
Sept-21$98,500
Oct-21 

 

i wrote a measure to to get the MAX sales figures where EOMONTH <= Today () - 1, however, this returns 100,000 which isn't what i want. I want to show the Actual figure for Current Month - 1. How do i return the sales figure for current month - 1? 

 

When i do EOMONTH = Today() - 1, i get blank. 

 

Thanks in advance. 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Andrea_Jess ,

calculate(sum(Table[Sales]), filter(Table, eomonth(Table[Date]) = eomonth(today(),-1)) )

 

other option with time intelligence

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Andrea_Jess ,

calculate(sum(Table[Sales]), filter(Table, eomonth(Table[Date]) = eomonth(today(),-1)) )

 

other option with time intelligence

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

Thanks so much! Worked like a charm!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors