Forum Discussion

Helper 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...

WolfBiber
8 years ago
find attached a working PBIX
https://1drv.ms/u/s!AuNGof2uT8Pphf1Y5xW3kM3XD3wbmQ

Microsoft Employee

8 years ago

Hi,

please provide Sample PBIX.

THX

Helper I

8 years ago

Hi,

I hope, posting a link like this is ok:

https://we.tl/0RGe4abBdH

I created two measures:

CountLastMonth = CALCULATE(COUNT(Invoices[InvNr]);DATEADD(DateTable[Date].[Date];-1;MONTH))

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

Many thanks!

WolfBiber
Microsoft 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
  Microsoft Employee
  8 years ago
  CountPreviousMonth = CALCULATE(COUNT(Invoices[InvNr]);PREVIOUSMONTH(DateTable[Date]))
  sorry, there was a 'Date' to much :D
  find correct formula above
  - legrand
    Helper I
    8 years ago
    Hi,
    now it shows me the table as a result but the final measure is still blank :smileysad:
    
    https://we.tl/QGyorreLyO
    
    Many thanks!
- legrand
  Helper I
  8 years ago
  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!

Related content