Assistance Need w DAX Calculation to Replace Blanks with the Value from Another Column

[Anonymous]

I have a table in which I am calculatiing a countdown of days from the start of an event to find the average days to address the event.  Once an event is addressed, a finished date is entered and the event completion duration can be calculated.  If an event hasn't been addressed, the days continue to accumulate in the calculation.  What I need is a measure that will divide the number of days an event is open divided by the overall count of records to get an average.  But if an event isn't closed, I need the measure to return a value I have calculated in another column which is counting the number of days between event start and today.  I have tried to use an IF statement to replace a blank with the field from the table but it won't work.  Example:

Average Days for Event = IF(DIVIDE([Event Completion Duration], [Overall Event Count], [Running Days]

When an event hasn't been addressed, it shows as blank in the Event Completion Duration column.  That's why I want it to then pull the value from the Running Days column but only in the case of a blank.  

I am new to DAX and this has been the most complicated thing I've tried to address so far.

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

According to your description, I create a sample and you can try like this.

1.Create the column “Event Completion Duration”.

2.Create the measure ”Average Days for Event”.

Best Regards,
Community Support Team _ kalyj

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

[Anonymous]

udhayakumarr28,

  An example would be as follows:

• Event ABC was created on 1/1/21 and completed on 1/5/21 (4 day duration)
• Event DEF was created on 1/1/21 and completed on 1/10/21 (9 day duration)
• Event GHI was created on 1/15/21 and has yet to be completed (Running total)
• Event JKL was created on 2/1/21 and completed on 2/9/21 (8 day duration)

  There are 4 total events with an average depending on the value from event GHI.  I have a measure that calculates the number of records overall and I have measures to calculate event duration and another return the running total based on today().  What I can't do is put them all together to give me the average days for completion including the event that has yet to be completed.  I can calculate the average number of days for completed events but I can't get further than that.  

  Does that make more sense?

[udhayakumarr28]

Hi @Anonymous, better you need to provide Sample Data and Expected results in an image so that we can easily understand your question.