Dynamic difference between dates present in a single column based on slicer selection

[ALobo94]

Hi,

I have a sample data in the following format in an excel.

Serial No	Tax Invoice	Stage 1	Stage 2	Stage 3
1	T1	3/23/2024	3/27/2024	4/6/2024
2	T2	3/28/2024	4/27/2024	4/29/2024
3	T3	4/1/2024	4/15/2024	4/16/2024

The data represents invoices which is tracked across 3 different stages of when the invoice reaches the different stages, which are in Date. Here Stage3 > Stage 2> Stage 1

I want the Average Date difference across all invoices between any 2 stages based on a slicer selection between stages.

For Eg: I have a slicer with Stages 1,2 & 3. When I select Stages 1 & 3, I need to get the Average of Date difference between the Stage 3 & Stage 1. 

So I need to get something below for Stage 1 to Stage 3. I just need the average, & it should change based on the stages I choose in the slicer. How do I go about this? Thanks in advance.

Invoice	Date Difference
T1	14
T2	32
T3	15
Avg	20.3

[ALobo94]

Thanks v-xinruzhu-msft for the help

[Ashish_Mathur]

Hi,

These measures work

Diff = 1*(max(Data[Date])-MIN(Data[Date]))

Measure = AVERAGEX(VALUES(Data[Tax Invoice]),[Diff])

Hope this helps.

[Anonymous · Accepted solution]

Hi @ALobo94 

You can refer to the following solution.

1.It is better that unpivot the column to the following format in power query.

2.Create the following measures.

theDateDifference =
VAR _maxdate =
    CALCULATE (
        MAX ( 'Table'[Value] ),
        ALLSELECTED ( 'Table' ),
        'Table'[Tax Invoice] IN VALUES ( 'Table'[Tax Invoice] ),
        'Table'[Stage] IN VALUES ( 'Table'[Stage] )
    )
VAR _mindate =
    CALCULATE (
        MIN ( 'Table'[Value] ),
        ALLSELECTED ( 'Table' ),
        'Table'[Tax Invoice] IN VALUES ( 'Table'[Tax Invoice] ),
        'Table'[Stage] IN VALUES ( 'Table'[Stage] )
    )
RETURN
    DATEDIFF ( _mindate, _maxdate, DAY )

Date Difference = AVERAGEX(VALUES('Table'[Tax Invoice]),[theDateDifference])

Then put the date difference measure to the visual.

Output

Best Regards!

Yolo Zhu

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