Filtering based on x days

[TBSST]

Hi all, 

i am trying to work on a dax function to show overdue reports. I currently have multipel IDs with different dates on the report and at what stage they conducted the report. I would like to take the last report they did, and count how many are overdue. 

Example: 

Client	Type	Date
1	Start	1/01/2023
2	Start	1/01/2023
3	Start	1/01/2023
1	Interim	1/02/2023
2	Interim	1/02/2023
1	End	1/03/2023

persume today is 1/3/23.

So in my final number, i want to identify based on the last report recorded, how many reports have not been done within the last 31 days, excluding clients who have an exited type. so final number here would be 1, because client 2 last report was within 31 days, however client 3 is overdue based on their last report, and exclding client 1 becuase their type is End. 

Thank you. 

[Mahesh0016 · Accepted solution]

@TBSST  I hope this helps you. Thank You.

It looks like you want to create a DAX measure in Power BI to calculate the number of overdue reports based on certain conditions. In your example, you want to count the clients whose last report was not done within the last 31 days and exclude clients with an "End" type report.

Here's how you can achieve this using DAX:

```dax
OverdueReportsCount =
VAR MaxDate = TODAY() -- Current date
VAR FilteredTable =
FILTER(
YourTableName, -- Replace with the actual name of your table
NOT(ISBLANK([Date])) && [Type] <> "End"
)
VAR ClientMaxDate =
SUMMARIZE(
FilteredTable,
[Client],
"LastReportDate", MAX([Date])
)
VAR OverdueClients =
FILTER(
ClientMaxDate,
[LastReportDate] <= MaxDate - 31
)
RETURN
COUNTROWS(OverdueClients)
```

Here's what the DAX measure does step by step:

1. The `FilteredTable` variable filters out rows where the date is blank or the report type is "End".

2. The `ClientMaxDate` variable calculates the maximum date for each client using the `SUMMARIZE` function.

3. The `OverdueClients` variable filters the `ClientMaxDate` table to include only clients whose last report date is more than 31 days ago.

4. Finally, the measure returns the count of rows in the `OverdueClients` table, which gives you the count of clients with overdue reports based on your criteria.

Make sure to replace `YourTableName` with the actual name of your table in Power BI. Also, make sure your date columns are recognized as dates in Power BI for this DAX code to work correctly.

You can create a new DAX measure in your Power BI report and use this formula. This measure will then display the count of overdue reports based on your criteria.

[TBSST]

Fantastic. Thank you for the response. This is indeed the solution i needed!