Forum Discussion

legrand's avatar
legrand
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...
Need Help
legrand's avatar
legrand
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's avatar
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's avatar
    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's avatar
    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!