Forum Discussion

hungryhippos1's avatar
hungryhippos1
New Member
5 years ago
Solved

Dynamic Average Aging based on DATEDIFF between column date and selected date value

Hi all, I'm trying to figure out how to find the average of a dynamic datediff between a column date field and a selected date value.

 

The idea would be that there is two tables, one that is at an issue level granularity like the following:

Categoryissue idstartdate
IT11/1/2020
IT22/2/2020
HR33/1/2020
HR4

1/4/2020

 

And another that would be a table that has various dates that will be used for slicing purposes.

 

The idea of how this would work is that I would be able to find the DATEDIFF (Days) of each issue id based on the selected date value chosen. From there, I would then aggregate the datediff via an average at the Category level. Something along the lines of:

 

 

=AVERAGE(DATEDIFF(Table1[startdate],SELECTEDVALUE(Table2[date]),DAY))

 

 

 

The big problem is that I don't know how I would properly input the startdate into the measure and get a proper datediff for each row to have it then rollup for the average. Any help would be greatly appreciated!

Need Help
  • v-robertq-msft's avatar
    v-robertq-msft
    5 years ago

    Hi, hungryhippos1 

    According to your description, you want to calculate the dynamic average value of datediff between the [startdate] and the selected date in Slicer, you can follow my steps:

    1. Create these two measures:
    Datediff =

var _selecteddate=SELECTEDVALUE(Table2[Date])

var _datediff=DATEDIFF(MAX('Table1'[startdate]),_selecteddate,DAY)

return

_datediff
    Average of datediff = AVERAGEX(values('Table1'[startdate]),[Datediff])
    1. Create a Slicer and place ‘Table2’[Date], a table and place columns like this:

     

    And you can get what you want, like this:

     

    You can download my test pbix file here

     

    Best Regards,

    Community Support Team _Robert Qin

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

1 Reply

  • v-robertq-msft's avatar
    v-robertq-msft
    Community Support
    5 years ago

    Hi, hungryhippos1 

    According to your description, you want to calculate the dynamic average value of datediff between the [startdate] and the selected date in Slicer, you can follow my steps:

    1. Create these two measures:
    Datediff =

var _selecteddate=SELECTEDVALUE(Table2[Date])

var _datediff=DATEDIFF(MAX('Table1'[startdate]),_selecteddate,DAY)

return

_datediff
    Average of datediff = AVERAGEX(values('Table1'[startdate]),[Datediff])
    1. Create a Slicer and place ‘Table2’[Date], a table and place columns like this:

     

    And you can get what you want, like this:

     

    You can download my test pbix file here

     

    Best Regards,

    Community Support Team _Robert Qin

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