Forum Discussion

legrand's avatar
legrand
Icon for Helper I rankHelper I
8 years ago
Solved

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 c...
Need Help
WolfBiber's avatar
WolfBiber
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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.

WolfBiber's avatar
WolfBiber
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago
CountPreviousMonth = CALCULATE(COUNT(Invoices[InvNr]);PREVIOUSMONTH(DateTable[Date]))

sorry, there was a 'Date' to much :D

find correct formula above