Urgent! How to calculate date range

[xiumi_hou]

Dear all,

I have a table like below :

Column 1	Received Date
A	1/1/2020
B	3/2/2020
C	8/2/2020
A	3/1/2020
B	1/3/2020
C	12/12/2020

Receiced date is a column in the table and I have a calculate measure called "the last refresh date" - this is usually is system date.

I would like to caclulate the average days between received date and last refresh date for A. (I would like both by calendar days and business days)

Thank you !!

[Icey]

Hi @xiumi_hou ,

Do amitchandak's measures work in your scenario?

Best Regards,

Icey

[Anonymous]

It is 7777 days from the start date to the end date, but not including the end date. Or 21 years, 3 months, 15 days excluding the end date. Or 255 months, 15 days excluding the end date.

[amitchandak]

@xiumi_hou 

You can have measures like these

Diff = datediff(table[Received Date],[last refresh date],DAY)
Avg Diff = AverageX(Summarize(Table, Table[Column 1],"_1",[Diff]),[_1])

[xiumi_hou]

Hi @amitchandak  Thank you so much for your help! I did send a message yesterday but it not successfully out.

That work for me. But when I create any measure, it all gave ERROR results:

What I would like to do it calculate the average days between referral received date and the next planned call date. The next planned call date is a measure created from another table. Below is my dax function:  

This is my next planned sessions measure: 

Next Plan Session Date =

VAR _Assess = "Assessment"

VAR _calc =

CALCULATE ( MIN( Calls[date_start] ),

FILTER (

Calls,

Calls[status] = "Planned"

&&

Calls[type_c]= _Assess

)

)

RETURN

_calc

This is my totay waiting days functions (I added this as a new column in case table): 

Total Waiting Days Column = SUMX (

FILTER (

d_date,

'd_date'[Date] >='Cases'[Cases_cstm.referral_received_date_c] && d_Date[Date]<=Next Plan Session Date ]), d_Date[ISBusiness days])

Please note only small amount of cases have plan session date.  THANK YOU!!

[Icey]

Hi @xiumi_hou ,

Please share me a dummy PBIX file, removing sensitive information, for specific test.

Best Regards,

Icey

[xiumi_hou]

UP