Forum Discussion
Helper IIDifference between set of dates on the same column
Hello. I need to calculate the difference between the dates of different applications:
| Field | Application | Date |
| P1 | 1st | 01/01/2024 |
| P2 | 1st | 01/05/2024 |
| P1 | 2nd | 01/20/2024 |
| P2 | 2nd | 01/22/2024 |
| P1 | 3rd | 02/10/2024 |
| P2 | 3rd | 02/11/2024 |
| P1 | 1st | 02/15/2024 |
| P2 | 1st | 02/15/2024 |
| P1 | 2nd | 02/27/2024 |
| P2 | 2nd | 02/28/2024 |
| P1 | 3rd | 03/10/2024 |
| P2 | 3rd | 03/11/2024 |
| P1 | 1st | 03/14/2024 |
| P2 | 1st | 03/16/2024 |
| P1 | 2nd | 03/20/2024 |
| P2 | 2nd | 03/24/2024 |
| P1 | 3rd | 04/02/2024 |
P2 | 3rd | 04/05/2024 |
For example, I need to take the dates of the 2nd application of P1, select the oldest of them, and subtract the oldest date of the 1st application of P1. Therefore, I need to take the dates of the 3rd application of P1, select the oldest of them, and subtract by the oldest date of the 2nd application of P1. In the truth table, there will be more types of application, such as 4th and 5th. And so on with all Fields.
Thanks for the reply from vigneshba , please allow me to provide another insight:
Hi jobf ,
Here are the steps you can follow：
1. Create calculated column.
Index = VALUE( LEFT( 'Table'[Application],LEN('Table'[Application])-2))2. Create measure.
Measure = var _current= MAXX( FILTER(ALL('Table'), 'Table'[Field]=MAX('Table'[Field])&&'Table'[Index]=MAX('Table'[Index])),[Date]) var _last= MAXX( FILTER(ALL('Table'), 'Table'[Field]=MAX('Table'[Field])&&'Table'[Index]=MAX('Table'[Index])-1),[Date]) return DATEDIFF( _last,_current,DAY)3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
5 Replies
Hi jobf - I hope you are expecting below result
Please follow below steps to achieve this.
I have just loaded the same dataset in Power BI and created a reference in Power Query and grouped this table with Field and Application to get Oldest Date in Power BI. I have created reference here not to disturb the Original Data but we can apply group by directly on the Original DataYou have to create 1 Calculated Measurement which I have created as Prior Oldest Date here to get the prior date for every group (Field and Application) and first item it will be blank as there are no prior dates available
Prior Oldest Date = CALCULATE(MAX('Use Case'[Oldest Date]), FILTER(ALLEXCEPT('Use Case', 'Use Case'[Field]), 'Use Case'[Oldest Date]<EARLIER('Use Case'[Oldest Date])))
You have to create one more Calculated Dimension to find the Date Difference between these 2 dates nowDate Difference = DATEDIFF('Use Case'[Prior Oldest Date], 'Use Case'[Oldest Date], DAY)Hope this helps you to do date difference between the dates in the same columnThanks for the reply from vigneshba , please allow me to provide another insight:
Hi jobf ,
Here are the steps you can follow：
1. Create calculated column.
Index = VALUE( LEFT( 'Table'[Application],LEN('Table'[Application])-2))2. Create measure.
Measure = var _current= MAXX( FILTER(ALL('Table'), 'Table'[Field]=MAX('Table'[Field])&&'Table'[Index]=MAX('Table'[Index])),[Date]) var _last= MAXX( FILTER(ALL('Table'), 'Table'[Field]=MAX('Table'[Field])&&'Table'[Index]=MAX('Table'[Index])-1),[Date]) return DATEDIFF( _last,_current,DAY)3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
jobfThere was an error. DAX comparison operations do not support comparing values of type Text with values of type Number. Consider using the VALUE or FORMAT function to convert one of the values.
- jobf
Hello. Thanks, your solution worked. I just have one question. In the true table, for example, there are some moments where there is a 1st application and 3rd application, but there is no 2nd application. I would like to know if there is a possibility of changing the measure so that, if the previous application (-1) does not exist, it subtracts from the column that came before it (-2).
