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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Microsoft Employee
Microsoft 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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.