topic Re: calculate the days between two date( but two dates in a columns) in DAX Commands and Tips

calculate the days between two date( but two dates in a columns)

Poo1405 — Mon, 11 Oct 2021 12:46:52 GMT

I need to calculate the days between two dates in a couple of rows (you can see clearly when seeing the screenshot below) base on other attributes like "leadid" and "campaign". my data set has lots of couples like this, the clear difference that can easily find is one row has "statuslead": ready to call and another is called and the date of its row, the rest are the same information.

I have consulted many other posts in the community about how to calculate the days between two dates but most of them are from 2 different columns.

However, it is not suitable for my case, I want to calculate the average of the days between 2 rows has the same information but different date based on leadid. In addition, as far as I can see, the row has statuslead: ready to call is always comes before the row has statuslead: called. Moreover, I also want the return result to be a large number or zero (>= 0) to eliminate the cases where rows have no 2 status to be able to match and calculate.

I need help from everyone, Thanks for taking the time to take a look at my post!!

I also share my .pbix file with everyone to easier to observe!!!
https://drive.google.com/file/d/1fDhVfvHzE6gKbL6OMFhomgoAqi7gWsLJ/view?usp=sharing

Re: calculate the days between two date( but two dates in a columns)

Icey — Thu, 14 Oct 2021 02:36:04 GMT

Hi Poo1405 ,

Please check if this is what you want:

DateDiff_Call = VAR LeadStatusCount = CALCULATE ( DISTINCTCOUNT ( 'file combine'[statusLead] ), ALLEXCEPT ( 'file combine', 'file combine'[leadid], 'file combine'[campaign] ) ) VAR t = FILTER ( ALLEXCEPT ( 'file combine', 'file combine'[leadid], 'file combine'[campaign] ), LeadStatusCount = 2 ) VAR Date_Ready_to_call = CALCULATE ( MAX ( 'file combine'[date1] ), FILTER ( t, [statusLead] = "Ready to call" ) ) VAR Date_Called = CALCULATE ( MAX ( 'file combine'[date1] ), FILTER ( t, [statusLead] = "Called" ) ) RETURN DATEDIFF ( Date_Ready_to_call, Date_Called, DAY )

Best Regards,

Icey

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

Re: calculate the days between two date( but two dates in a columns)

Poo1405 — Thu, 14 Oct 2021 03:49:56 GMT

I really appreciate your help; the number shown in the table is very similar to my expectations. Still, I have a problem doing other calculations based on that number (e.g., calculate the average, min, max) to serve statistics on each campaign, source, or calculate KPI for that number under the chart or graph. It didn't work.

From what I observe, the metrics datediff must appear together with the leadid to be displayed. However, I still want it to be a number that can be displayed independently, making it more suitable and easier to represent on the graph or chart.

Re: calculate the days between two date( but two dates in a columns)

Icey — Thu, 14 Oct 2021 08:16:05 GMT

Hi Poo1405 ,

For avg, max, etc., try this:

Avg by campaign = AVERAGEX ( VALUES ( 'file combine'[leadid] ), [DateDiff_Call] )

Max by campaign = MAXX ( VALUES ( 'file combine'[leadid] ), [DateDiff_Call] )

In addition, [Date_Diff] measure is calculated based on date1 of each leadid, if you want to show it independently, what are the implications of the results you expect to display?

Best Regards,

Icey

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

Re: calculate the days between two date( but two dates in a columns)

Poo1405 — Thu, 14 Oct 2021 09:02:47 GMT

Thank you so much for being so helpful; I intend to use the duration of two dates like a KPI to observe and evaluate the performance and quality of call center, so need it can be shown dependent on multi-view (real to source, campaign, date, ...) to so that can best improve my performance and planning.

Anyway, once again, I want to thank Icey for supporting me; maybe now the average, min, max index of duration (dateiff) can help me create a dashboard that can enhance overall observation of a sufficient assessment of the quality of the call center.

Re: calculate the days between two date( but two dates in a columns)

Icey — Thu, 14 Oct 2021 09:13:13 GMT

Hi Poo1405 ,

You are welcome. Glad to be able to help you. Wish you a happy life.😉

Best Regards,

Icey