cancel
Showing results 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

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-Year Sales 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.

1 ACCEPTED SOLUTION
Super User

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

2 REPLIES 2
Super User

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

Helper III

Thanks so much! Worked like a charm!

Announcements

#### 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 Monthly Update - June 2024

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

#### 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
Top Kudoed Authors