Counts rows between dates

[Rog_2901]

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_on], ">="&[@[Date-20]]))

and I want to put this line of thinking to PowerBi. 

I have in PowerBi "Incidents" table which contains "Date", "Sys_Created_on",  "New_Resolved", "WeekStart" columns and "Calendar"

11-20 days TEST =

COUNTROWS(

FILTER('Incidents',

'Incidents'[sys_created_on] <= max('Incidents'[week_start]) &&

'Incidents'[New_resolved_date] >= max('Incidents'[week_start])&&

'Incidents'[sys_created_on] >= max('Incidents'[week_start -20])&&

'Incidents'[sys_created_on] < max('Incidents'[week_start -10])

))

But also in this case I don't have correct values. 

Could you help me with this? How should I create this measure?

[Rog_2901]

Date is week_start (which is the column that appears in Incidents and Calendar, because it's simply the start of each week). I created a Calendar table so that I wouldn't have to compare it row by row, but when I used this table to some extent, I didn't get a satisfactory result either.

I would like the measure to calculate exactly like a formula in Excel - searching for data that meet the condition

[Rog_2901]

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.

[Anonymous]

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

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.  Explain the question and show the expected result.

[Anonymous]

Hi @Rog_2901 ,

Maybe you can try this DAX:

11-20 days TEST = 
VAR _start = MAXX(ALL('Incidents'), 'Incidents'[week_start])
VAR _start-20 = MAXX(ALL('Incidents'), 'Incidents'[week_start-20])
VAR _start-10 = MAXX(ALL('Incidents'), 'Incidents'[week_start-10])
RETURN
COUNTROWS(
FILTER(ALL('Incidents',
'Incidents'[sys_created_on] <= _start &&
'Incidents'[New_resolved_date] >= _start &&
'Incidents'[sys_created_on] >= _start-20 &&
'Incidents'[sys_created_on] < _start-10
))

If this DAX doesn't work:
Can you provide some sample data? Without sample data it's hard for me to know what your problem is. Also, could you please provide us with the incorrect result you got and the correct result you would like to get?

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.