Forum Discussion
calculate datediff column date using filters
hello,
I need help using DAX.
Her is my problem:
I have a table of operation and their date for multiple scenarios with column date and I want to do a datediff on this column using filters:
Example of my data :
| ID_OPERATION | ID_TYPE_SCENARIO | DATE_XX | DATE_YY |
| 1 | 1 | 01/01/2023 | 02/01/2023 |
| 1 | 2 | 02/01/2023 | 02/01/2023 |
| 1 | 3 | 10/01/2023 | 10/01/2023 |
| 2 | 1 | 11/01/2023 | 11/01/2023 |
| 2 | 2 | 12/01/2023 | 12/01/2023 |
| 2 | 3 | 13/01/2023 | 13/01/2023 |
I want to calculate for each operation the datediff (DATE_XX where ID_TYPE_SCENARIO =1 , DATE_XX where ID_TYPE_SCENARIO =2, DAY)
Same thing with the other date => for each operation the datediff (DATE_YY where ID_TYPE_SCENARIO =1 , DATE_YY where ID_TYPE_SCENARIO =2, DAY)
Thank you.
I've found the solution , I created a table with :
Table =VAR xxxx =SUMMARIZECOLUMNS('dwh BU'[LB_BU],'dwh OPERATION_SIMULATION'[CD_OPERATION],"datediff_XX", DATEDIFF ( CALCULATE ( FIRSTDATE( 'dwh OPERATION_SIMULATION'[DT_XX] ), 'dwh TYPE_SCENARIO'[LB_TYPE_SCENARIO] = "Premier validé" ),CALCULATE ( FIRSTDATE( 'dwh OPERATION_SIMULATION'[DT_XX] ), 'dwh TYPE_SCENARIO'[LB_TYPE_SCENARIO] = "Référence" ), DAY ) ,"datediff_YY", DATEDIFF ( CALCULATE ( FIRSTDATE( 'dwh OPERATION_SIMULATION'[DT_YY] ), 'dwh TYPE_SCENARIO'[LB_TYPE_SCENARIO] = "Premier validé" ),CALCULATE ( FIRSTDATE( 'dwh OPERATION_SIMULATION'[DT_YY] ), 'dwh TYPE_SCENARIO'[LB_TYPE_SCENARIO] = "Référence" ), DAY ))return xxxx
6 Replies
Anonymous ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.Hello,
You could add a column where you use this calclation column = Datediff( DATE_XX, DATE_YY, DAY )
So you'll have a column based with the how much days for each row, then you can visualise it by ID operation and can see how many days per each operation.If I answered your question, please mark my post as solution, Appreciate your Kudos👍
Diff_xx = VAR _OPERATION = 'Список1'[ID_OPERATION] VAR __SCENARIO = 'Список1'[ID_TYPE_SCENARIO] ВОЗВРАЩАТЬСЯ РАЗНИЦА ДАТ ( ВЫЧИСЛИТЬ ( ВЫБРАННОЕЗНАЧЕНИЕ ('Список1'[DATE_XX]), ФИЛЬТР ( ВСЕ ("Список1"), 'Список1'[ID_OPERATION] = _OPERATION && 'Список1'[ID_TYPE_SCENARIO] = __SCENARIO - 1 ) ), 'Список1'[DATE_XX], ДЕНЬ ) Разница_ГГ = VAR _OPERATION = 'Список1'[ID_OPERATION] VAR __SCENARIO = 'Список1'[ID_TYPE_SCENARIO] ВОЗВРАЩАТЬСЯ РАЗНИЦА ДАТ ( ВЫЧИСЛИТЬ ( ВЫБРАННОЕЗНАЧЕНИЕ ("Список1" [ДАТА_ГГ]), ФИЛЬТР ( ВСЕ ("Список1"), 'Список1'[ID_OPERATION] = _OPERATION && 'Список1'[ID_TYPE_SCENARIO] = __SCENARIO - 1 ) ), 'Список1'[DATE_YY], ДЕНЬ )I've found the solution , I created a table with :
Table =VAR xxxx =SUMMARIZECOLUMNS('dwh BU'[LB_BU],'dwh OPERATION_SIMULATION'[CD_OPERATION],"datediff_XX", DATEDIFF ( CALCULATE ( FIRSTDATE( 'dwh OPERATION_SIMULATION'[DT_XX] ), 'dwh TYPE_SCENARIO'[LB_TYPE_SCENARIO] = "Premier validé" ),CALCULATE ( FIRSTDATE( 'dwh OPERATION_SIMULATION'[DT_XX] ), 'dwh TYPE_SCENARIO'[LB_TYPE_SCENARIO] = "Référence" ), DAY ) ,"datediff_YY", DATEDIFF ( CALCULATE ( FIRSTDATE( 'dwh OPERATION_SIMULATION'[DT_YY] ), 'dwh TYPE_SCENARIO'[LB_TYPE_SCENARIO] = "Premier validé" ),CALCULATE ( FIRSTDATE( 'dwh OPERATION_SIMULATION'[DT_YY] ), 'dwh TYPE_SCENARIO'[LB_TYPE_SCENARIO] = "Référence" ), DAY ))return xxxx
