Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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:  Ex...
General Comment
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    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
     
     
Ahmedx's avatar
Ahmedx
Icon for Super User rankSuper User
3 years ago

 

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],
         ДЕНЬ
     )

https://dropmefiles.com/76kiC