Forum Discussion
Counts rows between dates
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.
Hi Rog_2901 ,
Can you explain which column does '"&[@Date]' refer to? Is Incidents[week_start] or Calendar[Date] or Calendar[week_start]?
Based on the DAX you provided earlier, you don't seem to be using the Calendar table at all, I don't quite understand what the purpose of this table is?
Also, based on the screenshot of the data you provided, it appears that there is hardly any line of data that would satisfy this one sentence of DAX code that you provided earlier:
This code in measure generally performs a comparison between dates on the same line, i.e., [sys_created_on] is less than or equal to [week_start] on the current line, but the data you provided all seem to be less than or equal to.
Could you please state your desired outcome in text form rather than code in excel? Thanks!
Best Regards,
Dino Tao