Calculating the age between a date field and a date selected in a slicer

[Anonymous]

Hi,

I have a field called ReceiptDate and i want to find the numerical difference between that date and one selected by a slicer.

I've managed to capture my selection on the slicer by using SELECTEDVALUE() but can't seem to figure out the measure to get the difference when the results are displayed in a table.

Any ideas?

Ben

EDIT: 

Resolved with the following:

MaxMeasure = (MAX('Point in Time Data'[Age From Date])-[SelectedPointInTime])*1

[v-xicai]

Hi @Anonymous ,

You can try this measure.

DATEDIFF =
   var SelectedDate = SELECTEDVALUE(SlicerTable[SelectedDate])
   var CurrentDate = SELECTEDVALUE(Table1[ReceiptDate])

   return

   DATEDIFF(CurrentDate, SelectedDate, DAY)

Best Regards,

Amy

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

[Anonymous]

Hi @v-xicai ,

Thanks for looking at this for me.

That didn't work unfortunately. I'm not selecting the receipt date, it's just a field in my table that i want to compare with my selected date

I've attached a picture to hopefully demonstrate what i'm trying to accomplish

[Ashish_Mathur · Accepted solution]

Hi,

Try this measure

=MAX([Age From Date])-[SelectedPointInTime]

Hope this helps.

[Anonymous]

Hi @Ashish_Mathur 

This does give me an age but i need it calcualte for each date in my table, not just the highest date.

Thanks

Ben

[Ashish_Mathur]

Hi,

My formula should work for each data in the Table.

[Anonymous]

Hi @Ashish_Mathur 

And so it does! It returned the result as a date which caused me confusion initially, after changing it back to a number I get the right result! Below is the measure I used:

MaxMeasure = (MAX('Point in Time Data'[Age From Date])-[SelectedPointInTime])*1

Thanks very much for your help!

[Ashish_Mathur]

You are welcome.

[v-xicai]

Hi @Anonymous ,

Can't it meet your demand using DATEDIFF([Age from date], [SelectedPointInTime], DAY) directly? When you select a date in slicer, it will return the result in the second table visual of your screenshot. Or could you please show your desired output in sample data screenshot for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Amy

[Anonymous]

Hi @v-xicai ,

I've applied filters to my report, and i'm trying to make the age value in table on the left equal the age in the table on the right. I've uploaded a copy of the .pbix file here

DATEDIFF I requires a single value in order to calculate. DATEDIFF([Age from date], [SelectedPointInTime], DAY) as a measure doesn't work, as I believe some kind of filter is required on [Age from date].

I've tried doing DATEDIFF([Age from date], [SelectedPointInTime], DAY) as a calculated column, and while my measure [SelectedPointInTime] is capturing my selection the calculated column doesn't recalculate so it defaults to the current month...

Thanks for your help 

Ben