Calculate days between DateAdded column and Chosen Date

[Anonymous]

Good day

I would like to calculate as a column or as a measure, the days duration between the DateAdded column in an existing table and date chosen eg respective month end dates via a slicer.

The problem is that I can't calculate how many days outstanding a library book is when it has not been returned. Currently the year 3000 is being used until there is a return date.

Please help!

[amitchandak · Accepted solution]

@Anonymous , Try like

measure =
var _max = maxx(allselected('Date'), 'Date'[Date])
return
CALCUALTE(datediff(max(Table[DateAdded]),_max, day), filter(Table, year(table[DateAdded])<3000 ))

You have to force a row context for the correct average

to refer use values or summarize:https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Difference-Across/ba-p/934397#M451

[Anonymous]

Hi @amitchandak Amit,
thank you for the response. It worked thank you.

I need to take it a step further, if the claim has been finalised before the selected date ("fulldate" above), then I need it to calculate those days in stead of from dateadd to selected date.

Also, how would I create an additional measure whereby I calculate the average duration for say a range of products rather than specific claims?

[Anonymous]

Hi @amitchandak 

Can you help me further, please?