Forum Discussion
Dynamic difference between dates present in a single column based on slicer selection
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 |
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.
3 Replies
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.
Hi,
These measures work
Diff = 1*(max(Data[Date])-MIN(Data[Date]))Measure = AVERAGEX(VALUES(Data[Tax Invoice]),[Diff])Hope this helps.
Thanks v-xinruzhu-msft for the help
