Filter by previous month (January minus one not working as expected)

[Madumi]

I wondered if someone could point me in the right direction. I am trying to obtain the sum of last month's project values using the following measure:

Previous Month = var current_month= MONTH(TODAY()) return CALCULATE(SUM(cr6a2_Project[Project Value]),FILTER(cr6a2_Project,MONTH(cr6a2_Project[cr6a2_approxclosedate])=current_month -1))

 Entity is cr6a2_Project, date field is cr6a2_approxclosedate, and value field in $ is Project Value

Do I also need to specify current year?

And how would I specify December of the previous year, when the current date is January?

Thanks!

[Fowmy · Accepted solution]

@Madumi 

You can try the measure this way:

Previous Month = 
var prev_month= FORMAT(EOMONTH(TODAY(),-1), "yyyymm") return 
CALCULATE(
    SUM(cr6a2_Project[Project Value]),
    FILTER(
        cr6a2_Project,
        FORMAT(cr6a2_Project[cr6a2_approxclosedate], "yyyymm")=prev_month
    )
)

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[littlemojopuppy]

Going to suggest you try this instead...

Current Month = SUM(cr6a2_Project[Project Value])

Previous Month = 
	CALCULATE(
		[Current Month],
		PREVIOUSMONTH('Calendar'[Date])
	)

Previous Month Alternative = 
	CALCULATE(
		SUM(cr6a2_Project[Project Value]),
		PREVIOUSMONTH('Calendar'[Date])
	)

This assumes you have a date table named "Calendar" and it is marked accordingly.

The measure [Current Month] calculates the Project Value in the current month.  The [Previous Month] measure uses time intelligence to shift the filter context to one month earlier.  Makes it much simpler to accomplish.  Or you can use just the [Previous Month Alternative] instead and still rely on time intelligence.

Hope this helps!  🙂