cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
legrand
Helper I
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

9 REPLIES 9
WolfBiber
Microsoft
Microsoft

Hi,

please provide Sample PBIX.

 

THX

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!

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. 

 

Unbenannt.png

For correct function of measure 

CountPreviousMonthFromToday 

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

Hope it helps.

Greetings.

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

sorry, there was a 'Date' to much 😄

find correct formula above

Hi,

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

 

https://we.tl/QGyorreLyO

 

Many thanks!

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

you're welcome

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!

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors