cancel
Showing results for
Did you mean:
Regular Visitor

## How to combine calculate and EOMONTH Filter

Hello, i have the following Measure

last month = calculate(sum('Table 1'[Revenue]),
'Table 1', eomonth('Table 1'[ReportDate],-1))
or
last month = calculate(sum('Table 1'[Revenue]),
'Table 1', 'Table 1'[ReportDate] = eomonth('Table 1'[ReportDate],-1))

With this measure i want to generate the revenue of the last day of the previous month. I have tried multiple adjustment but it doesnt seem to work. It works with 'Today()' but i want that viewers/users can change the 'reportdate' (with a filter) and that the PBI table will generate information about the last 3 months (on the last day) so i have multiple measures like this last month, 2nd last month, 3th last month.
Im looking forward to your solutions!

1 ACCEPTED SOLUTION
Super User

Hi @JayproviBI

last month =
CALCULATE (
SUM ( 'Table 1'[Revenue] ),
'Table 1'[ReportDate] = EOMONTH ( MAX ( 'Table 1'[ReportDate] ), -1 )
)
4 REPLIES 4
Super User

Hi @JayproviBI

last month =
CALCULATE (
SUM ( 'Table 1'[Revenue] ),
'Table 1'[ReportDate] = EOMONTH ( MAX ( 'Table 1'[ReportDate] ), -1 )
)
Regular Visitor

Your the man! Thanks, Highly appreciated. It works!

Continued Contributor

Hi @JayproviBI ,

I think the second meaure could work please try to create the EOM meaure and then use it in your measure.

EOM = eomonth('Table 1'[ReportDate],-1)

last month = calculate(sum('Table 1'[Revenue]),
'Table 1''Table 1'[ReportDate] = EOM)

Regards,
Nikhil Chenna

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Regular Visitor

Thank you for your reaction. Unfortunately i cant create this measure because i cant use directly eomonth and connect to the table. When i use the filter function like '

EOM.lastmonth = filter('table 1', eomonth('table 1'[ReportDate],-1))'

and use it on my primary measure i get an error message

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors
Top Kudoed Authors