Forum Discussion

SebSchoon1's avatar
SebSchoon1
Post Patron
3 years ago
Solved

Use Date diff with dates obtaines from Userelationship

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 !! 😛

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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