Forum Discussion
Solved
Calculate Date diff based on selected category
Hi
In the below table i want to calculate date difference keeping one category in Day as 0, in this case Day 0 as 0 and it should considering for substrating other dates eiher + or -, i will be having mutliple ID, can you please help
| ID | Day | Date | Diff |
| 1001 | Day -15 | 15-Aug-21 | -31 |
| 1001 | Day -10 | 25-Aug-21 | -21 |
| 1001 | Day 0 | 15-Sep-21 | 0 |
| 1001 | Day 15 | 1-Oct-21 | 16 |
| 1001 | Day 24 | 30-Oct-21 | 45 |
| 1001 | End | 15-Dec-21 | 75 |
| 1002 | Day -15 | 25-Aug-21 | -21 |
| 1002 | Day -10 | 30-Aug-21 | -16 |
| 1002 | Day 0 | 15-Sep-21 | 0 |
| 1002 | Day 15 | 25-Oct-21 | 40 |
Regards
Murali
Hi dmurali1829
You can create a column with below DAX.
Date Diff = VAR _day0 = MAXX ( FILTER ( TableName, TableName[ID] = EARLIER ( TableName[ID] ) && TableName[Day] = "Day 0" ), TableName[Date] ) RETURN DATEDIFF ( _day0, TableName[Date], DAY )Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
2 Replies
ID Day Date Diff 1001 Day -15 15-Aug-21 -31 1001 Day -10 25-Aug-21 -21 1001 Day 0 15-Sep-21 0 1001 Day 15 1-Oct-21 16 1001 Day 24 30-Oct-21 45 1001 End 15-Dec-21 75 1002 Day -15 25-Aug-21 -21 1002 Day -10 30-Aug-21 -16 1002 Day 0 15-Sep-21 0 1002 Day 15 25-Oct-21 40 Hi dmurali1829
You can create a column with below DAX.
Date Diff = VAR _day0 = MAXX ( FILTER ( TableName, TableName[ID] = EARLIER ( TableName[ID] ) && TableName[Day] = "Day 0" ), TableName[Date] ) RETURN DATEDIFF ( _day0, TableName[Date], DAY )Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
