Use Date diff with dates obtaines from Userelationship

[SebSchoon1]

Hello Guys,

I have a huge fact Table which contains movement Type and movement Date.

I have created two columns to get The Order Dates, and Invoiced Dates here below the two formulas

Date Commandes fournisseurs =

 if('Lignes de mouvement'[LIBELLE NATURE DE MOUVEMENT]="Commande fournisseur",

                 'Lignes de mouvement'[DATE PIECE],

                            BLANK())

AND 

Date Factures fournisseurs = 

if('Lignes de mouvement'[LIBELLE NATURE DE MOUVEMENT]="Facture fournisseur",

                       'Lignes de mouvement'[DATE PIECE],

                                             BLANK())

Then i have made some inactive relationships between Calendar[Date]

And these two columns  to know quantities ordered, and invoiced quantities.

Quantité de commande =

 

CALCULATE([Quantité],'Lignes de mouvement'[LIBELLE NATURE DE MOUVEMENT]="Commande fournisseur",

 

USERELATIONSHIP('Calendrier'[Date],'Lignes de mouvement'[Date Commandes fournisseurs]))

AND 

Quantité facturée =

 CALCULATE([Quantité],'Lignes de mouvement'[LIBELLE NATURE DE MOUVEMENT]="Facture fournisseur",



USERELATIONSHIP(Calendrier[Date],'Lignes de mouvement'[Date Factures fournisseurs]))

Which provie me this result

I would like to get the Number of days between the two dates, Ordered and invoiced dates 

Even with this view it is ok 

 any ways to realize this?

many thanks for any help !! 😛

[v-shex-msft · Accepted solution]

HI @SebSchoon1,

You can try to use the following measure formula to get the different based on current movement type and date:

Diff =
VAR currDate =
    MAX ( 'Lignes de mouvement'[DATE PIECE] )
VAR currType =
    SELECTEDVALUE ( 'Lignes de mouvement'[LIBELLE NATURE DE MOUVEMENT] )
VAR _start =
    SWITCH (
        currType,
        "Commande fournisseur", currDate,
        "Facture fournisseur",
            CALCULATE (
                MAX ( 'Lignes de mouvement'[DATE PIECE] ),
                FILTER (
                    ALLSELECTED ( 'Lignes de mouvement' ),
                    [LIBELLE NATURE DE MOUVEMENT] <> currType
                        && [DATE PIECE] < currDate
                )
            )
    )
VAR _end =
    SWITCH (
        currType,
        "Commande fournisseur",
            CALCULATE (
                MIN ( 'Lignes de mouvement'[DATE PIECE] ),
                FILTER (
                    ALLSELECTED ( 'Lignes de mouvement' ),
                    [LIBELLE NATURE DE MOUVEMENT] <> currType
                        && [DATE PIECE] > currDate
                )
            ),
        "Facture fournisseur", currDate
    )
RETURN
    DATEDIFF ( _start, _end, DAY )

Regards,

Xiaoxin Sheng