Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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
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
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |