Skip to main content
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

Regular Visitor

Dax 2 or 3 previous month count formula



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 






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


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?



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



Helpful resources

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.