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!