Dax 2 or 3 previous month count formula

[lewistremonti]

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

[v-danhe-msft]

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?

Regards,

Daniel He

[lewistremonti]

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