Forum Discussion

Rog_2901's avatar
Rog_2901
Frequent Visitor
2 years ago

Counts rows between dates

Hi, I have a function created in Excel: (=COUNTIFS(INCIDENTS[sys_created_on], "<="&[@Date], INCIDENTS[NEW resolved], ">="&[@Date], INCIDENTS[sys_created_on], "<"&[@[Date-10]], INCIDENTS[sys_created_...
Need Help
Show and Tell
Tips and Tricks
Rog_2901's avatar
Rog_2901
Frequent Visitor
2 years ago

I have an Incidents table with such data:

 

and Calendar table:

 

What I want is to create a table in PowerBi, similar to the one in Excel:

 

 

So, to explain: I want to have a column with week_start and next to it a columns with the categories: 1-10 days, 11-20 days, 21-30 days, 31-50 days and 51+ days. The values in the columns are calculated based on the:

 

1:10 days: =COUNTIFS(Table1[sys_created_on], "<="&[@Date], Table1[NEW resolved], ">="&[@Date], Table1[sys_created_on], "<"&[@Date], Table1[sys_created_on], ">="&[@[Date-10]])

 

11-20 days: =COUNTIFS(Table1[sys_created_on], "<="&[@Date], Table1[NEW resolved], ">="&[@Date], Table1[sys_created_on], "<"&[@[Date-10]], Table1[sys_created_on], ">="&[@[Date-20]])

 

I tried to create measures for these conditions in PowerBi, but the table only shows values for a specific day (they are too low) - not for the ranges defined in the mesures.