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 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!

Need Help

9 Replies

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