Calculate Average of DATEDIFF between Orderdate & Dateselected from slicer per Region

[Anonymous]

Hi,

I have Orders table that has Order Date, Ship Date, Region,Category,Sub-Category etc..

Order Date has data from 1/1/2014 to 30/09/2019.

I would like to show Average of DateDiff by Region. This average should be b/w Order Date & Date selected from slicer (user can choose any date from slider).

What I did is I have created a new table and used calendarauto() function to populate date info and named as Period Close.

So Now I am looking at the diff b/w Period Close (user selected date) & Order Date (Existing date from orders) and created a new Measure, Column for datediff as below,

New Measure--> Dateselected = selectedvalue (Date[period Close])

New Column --->Datediff = datediff(Orderdate,Dateselected,day) - but it's giving me blank.

Once if i get the data for Datediff column then I can define aggregation as Average that gives me Average of No of days diff.

Can anyone suggest me the best solution to achieve this requirement

A quick help would be much appreciated.

[PattemManohar]

@Anonymous  In your case, you can't use the Calculated Columns to dynamically dervie value based on the slicer selection. Try creating measures.

[Anonymous]

Hi,

Thanks for your response. But I would like to create a calculation to get Avg of datediff using some filtering conditions.

Example calculations:

Calculate (Average(DateDiff),filter(Orders,Category = "Furniture",Clearing Posting Date<=DateSelected))

To get this, DateDiff should be column, If I create measure I can't show Average of Datediff.

I managed to achieve this by creating parameter in query editor instead creating table using calendarauto function. it gives me expected value for average of Datediff but here the problem is everytime I should go to edit parameter and choose the date. Even if i did that it's not refreshing with selected value from edit parameter until I do manual refresh.

Suppose if parameter works somehow I am not sure how to change the parameter in Services.

To avoid this, I am trying to find a way to get the Datediff column.

Please suggest me better way to solve this issue (either using cretae table or parameter).

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

You could use "AVERAGEX" to calculate average for a measure.

In my test, i create a date table without relationship with your table

use the "date" column from this table to a slicer, select "before" from the drop down list.

create a measure to show the max date for the date slicer.

selected date = MAX('date'[Date])

Then create a measure for datediff

datediff = DATEDIFF(MAX('Table'[order date]),[selected date],DAY)

In my table, assume "ship date" refers to "Clearing Posting Date", create a measure to achieve the average as your measure expected.

average =
AVERAGEX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[category] = "cate1"
            && [ship date] <= [selected date]
    ),
    [datediff]
)

You can check whether my result is correct by [sum total]/[count]

[sum total]- sum the [datediff] which meets the conditions

[count]-count the [datediff] which meets the conditions

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-juanli-msf

Thanks for the solution. It's working completely fine.