Removing status' from average time calc

[bgepps]

In my data set, i have the following columns of data that are being pulled in via a database.

Job Status

Status start date

Status end date

I have been able to calculate the average time for each job by doing using a datediff formula; Day Diff = DATEDIFF (job_status_history[status_start_date], job_status_history[status_end_date],DAY), and using the average measures in the reporting elements.

However, there are some job status that i would like to exclude from this calculation. Does anyone know how i would add a measure/formula to my table to keep the day diff calculation, but exclude certain status in the 'status' column? For example, 'client approved'.

[tringuyenminh92]

Hi @bgepps,

Choose Edit Query, right click in Job Status column, you will have some filter options and please try with it. if it's working for your case we could try another approach with DAX 

[bgepps]

Hi @tringuyenminh92 - would this remove those status' from the other reports i have in my deck, though?

Or would i have to copy the table, and then filter out the status in the second table, with the same time difference formula's mentioned in my original post?

Appreciate your help,

Ben

[tringuyenminh92]

Hi @bgepps,

Yes, this is filtering dataset, reports using this source (this table) will be filtered these data.

[bgepps]

Hi @tringuyenminh92 - so if i follow my suggestion above, it should give me the information i require, and continue updating as the SQL database is populated with data?

If so i will try copying to a new table later and will keep you updated.

Thanks,

Ben

[tringuyenminh92]

Hi @bgepps,

Just try and let us know if it's not working as your expectation.