Calculate average since a specific date (SAMPLE DATA INCLUDED)

[water-guy-5]

Hello,

I posted last week but have provided a better layout of what it is I am trying to solve.

I have a list of facilities, and each one has a date for whena certain "limit" was established. Basically saying that on the "Submittal Date", we are now tracking certain values to monitor exceedances. I have also calculated the difference from the submittal date and today as I thought that might help with what I am trying to solve. For this example, lets use the highlighted row and call this FACILITY A.

So, my goal is to find the average result since the day of the submittal. However, I have a table with daily results since the creation of the facility. So, lets say that I have daily readings since the end of 2020 (see below). What I want is the average of the discharge column only from the submittal date, in this case 7/21/2022, or the last 137 days (as seen in the far right column above).

If I were to make a measure, all I am able to solve is for a certain number of days. This does not allow me to customize the averages based on each facilities differing submittal date. The average result will change if I do this versus what I want which is to solve for the average since the submittal date.

Any help?

[v-yueyunzh-msft · Accepted solution]

Hi , @water-guy-5 

According to your description, you want to calculate the "average since a specific date" , I do not know your table field and the relationships so i create test data like this:

Here are the steps that i hope it can help you :
(1)We can create a measure like this:

Average = var _cur_SubmittalDate = MAX('FACILITY'[Submittal Date])
var _curFacility = MAX('FACILITY'[FACILITY])
var _days = SUM('FACILITY'[Days Since Submittal])
var _t = FILTER( ALLSELECTED('Table') , 'Table'[FACILITY] = _curFacility && 'Table'[Date] <= _cur_SubmittalDate && 'Table'[Date]>_cur_SubmittalDate - _days)
return
DIVIDE(SUMX(_t , [Discharge]),_days)

(2)Then we can put it on the visual and the result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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