Forum Discussion
Power Bi Last 30 days filter
Hi,
I have an issue where I have a formula to calculate the days between a submission and then the acceptance of a request. Off of this, I have an average to get the average number of days overall. I want to filter this so it only takes into account requests submitted in the last 30 or 60 days for example.
I have tried filters but the average number does not change.
Can anyone help please?!?!?!
1 Reply
To filter your average calculation to only include requests submitted in the last 30 or 60 days, you can use a measure with a FILTER function to restrict the data used in the calculation. Here's an example measure that calculates the average number of days between submission and acceptance for requests submitted in the last 30 days:
Average Days Last 30 Days = VAR Last30Days = CALCULATETABLE('YourTable', DATESBETWEEN('YourTable'[Submission Date], TODAY()-30, TODAY())) RETURN AVERAGEX(FILTER(Last30Days, 'YourTable'[Acceptance Date] <> BLANK()), 'YourTable'[Days Between Submission and Acceptance])This measure first uses the CALCULATETABLE function to filter the data in 'YourTable' to only include requests submitted in the last 30 days. It then uses the FILTER function to further restrict the data to only include requests that have an acceptance date (i.e. the request has been accepted). Finally, it calculates the average number of days between submission and acceptance for the filtered data using the AVERAGEX function.
You can adjust the number of days by changing the second and third arguments of the DATESBETWEEN function. For example, to filter requests submitted in the last 60 days, you would use TODAY()-60 instead.
