cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

Previous Month without filter

Hi,

I want to calculate the invoice count or average order value of the previous month. I got this working with either DATEADD() or PREVIOUSMONTH() but only when filtering my Power BI sheet to the current month (slicer or filter on the right hand side).

When not filtering to the current month, the number shows "Blank" when using PREVIOUSMONTH() or the count of all invoices - 1 Month when using DATEADD().

Is there a solution without having to filter the report?

Many thanks!

1 ACCEPTED SOLUTION
Employee
9 REPLIES 9
Employee

Hi,

please provide Sample PBIX.

THX

Helper I

Hi,

I hope, posting a link like this is ok:

https://we.tl/0RGe4abBdH

I created two measures:

CountPreviousMonth = CALCULATE(COUNT(Invoices[InvNr]);PREVIOUSMONTH(DateTable[Date].[Date]))

Many thanks!

Employee

Hey,

easiest way is just to add another measure:

`CountPreviousMonthFromToday = CALCULATE([CountPreviousMonth];DateTable[Date]= Today())`

The reason is that Powerbi doesnt know for a single value what is the last passed month. Your Measure

`CountPreviousMonth = CALCULATE(COUNT(Invoices[InvNr]);PREVIOUSMONTH(DateTable[Date].[Date]))`

returns a table.

For correct function of measure

`CountPreviousMonthFromToday `

you have to extend your date table to minimun until today.

Hope it helps.

Greetings.

Employee
`CountPreviousMonth = CALCULATE(COUNT(Invoices[InvNr]);PREVIOUSMONTH(DateTable[Date]))`

sorry, there was a 'Date' to much 😄

find correct formula above

Helper I

Hi,

now it shows me the table as a result but the final measure is still blank

https://we.tl/QGyorreLyO

Many thanks!

Employee
Helper I

This seems to be working, also in my live worksheet, many thanks!!

Employee

you're welcome

Helper I

Hi,

thanks for the explanation. I tried your solution but the new measure still shows blank. I attached the modified PBIX.

https://we.tl/0z5zEexwXK

Many thanks!