Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
lewistremonti
Regular Visitor

Dax 2 or 3 previous month count formula

Hi,

 

Im using the below code to calculate counts of cases recieved in the previous month and placeing this into a matrix to show the origin of the case.

 

PriorMonthCount = CALCULATE(COUNT(SQLDatabase2[case_code]),PREVIOUSMONTH(DATESMTD(SQLDatabase2[DateCaseCreated])))

 

The issue im having is showing the above but for the month previous to that e.g. This month is October i want to show August and September, but i want this to be dynamic and update itself so when it is November the visualiation shows September and October.

 

I can use the below formula to count the amount of cases recieved 2 months ago however when i put this into the Origin matrix with the previous month the error "Cant Display The Visual" appears,

 

proirmonth2 = CALCULATE(COUNT('Case Outcome'[case_code]),DATESMTD(DATEADD(SQLDatabase2[DateCaseCreated],-2,MONTH)))

 

All the data is from a SQL database and in the same table so i do not think the issue is the relationships,

 

Anyhelp would be appricated 

 

Thanks 

 

Lewis

 

2 REPLIES 2
v-danhe-msft
Employee
Employee

Hi @lewistremonti,

What kind of conection mode you are using with your report? Import or Direct Query? I have tested below measure and it could work on my side:

proirmonth2 = var current_month= MONTH(MAX('Table1'[DateCaseCreated])) 
return  CALCULATE(COUNT(Table1[case_code]),FILTER('Table1',MONTH('Table1'[case_code])<current_month -2))

1.PNG

You could download the pbix file to have a view and if it could not meet your requirements, could you please offer me some sample data and post your desired result?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-danhe-msft

 

Thanks for the reply i have tried you solution and it didnt work for me brought back a total over 16000 and it should have been 1660 ish so not sure the issue.

 

The connection mode i was using is import query. however i created the data i needed in SQL and imported into power bi for ease.

 

Thanks for the support

 

Lewis 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors