Forum Discussion

yamina's avatar
yamina
Icon for Helper I rankHelper I
1 year ago
Solved

Help dax while adding column

Hi    I need to duplicate these two excel formulas in power bi deskstop by adding two news colums in my table  Can you help Me ?    Date_creation_DACRI N_dossier_NUDOS N_prestation_RGOUV M...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi yamina ,

     

    Please try following Dax formulars.

    Min date = 
VAR CurrentIndex = 'Table'[Index]
VAR PreviousDossier = MAXX(FILTER('Table', 'Table'[Index] = CurrentIndex -1), 'Table'[N_dossier_NUDOS])
VAR CurrentDossier = 'Table'[N_dossier_NUDOS]
RETURN
IF(
    PreviousDossier = CurrentDossier && CurrentDossier <> BLANK(),
    MINX(FILTER('Table', 'Table'[N_dossier_NUDOS] = CurrentDossier), 'Table'[Date_creation_DACRI]),
    BLANK()
)
    Condition retour = 
VAR CurrentDossier = 'Table'[N_dossier_NUDOS]
VAR CurrentIndex = 'Table'[Index]
VAR FirstDossierIndex = MINX(FILTER('Table', 'Table'[N_dossier_NUDOS] = CurrentDossier),'Table'[Index])
VAR FirstDossierCreationDate = CALCULATE(SELECTEDVALUE('Table'[Date_creation_DACRI]), 'Table'[Index] = FirstDossierIndex , ALL())
VAR CurrentPresentation = 'Table'[N_prestation_RGOUV]
VAR PreviousPresentation =  MAXX(FILTER('Table', 'Table'[Index] = CurrentIndex -1), 'Table'[N_prestation_RGOUV])
RETURN

IFERROR(
    IF( 'Table'[Date_creation_DACRI] - FirstDossierCreationDate >= 0 && 'Table'[Date_creation_DACRI] - FirstDossierCreationDate <= 30 && CurrentPresentation >= PreviousPresentation +1,
        "Retour dans les 30 jours",
        BLANK()
    ),
    BLANK()
)

     

    The final result is as follows. Hopefully it will meet your needs.

     

    Please see the attached pbix for reference.

     

    Best Regards,
    Dengliang Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Thanks for the reply from rajendraongole1, please allow me to provide another insight.

Hi yamina ,

 

Please refers to the following steps.

Add an index column to the table in the Power Query editor.

 

Use the following DAX formulas to create the 'Min date' column and the 'Condition retour' column.

 

 

Min date = 
VAR CurrentIndex = [Index]
VAR PreviousDossier = MAXX(FILTER('Table', [Index] = CurrentIndex -1),[N_dossier_NUDOS])
VAR CurrentDossier = 'Table'[N_dossier_NUDOS]
RETURN
IF(
    PreviousDossier = CurrentDossier && CurrentDossier <> BLANK(),
    MINX(FILTER('Table', [N_dossier_NUDOS] = CurrentDossier), [Date_creation_DACRI]),
    BLANK()
)

 

Condition retour  = 
VAR CurrentIndex ='Table'[Index]
VAR CurrentPresentation = 'Table'[N_prestation_RGOUV]
VAR PreviousPresentation =  MAXX(FILTER('Table', [Index] = CurrentIndex -1), [N_prestation_RGOUV])
RETURN
IFERROR(
    IF( [Date_creation_DACRI] - [Min date] >=0  && [Date_creation_DACRI] - [Min date] <= 30 && CurrentPresentation >= PreviousPresentation +1,
        "Retour dans les 30 jours",
        BLANK()
    ),
    BLANK()
)

 

The final result is as follows. Hopefully it will meet your needs.

 

Please see the attahced pbix for reference.

 

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.