Forum Discussion

ka047's avatar
ka047
Icon for Helper I rankHelper I
5 years ago
Solved

DATEDIFF DAX measure

I am trying to calculate the difference between 2 date fields. Both are in Fact tables & not created measures. AVERAGEX or SUMX won't work for me. Below is what I have so far, if someone could help m...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi ka047 ,

     

    The SUMMARIZE function returns a summary table. If you want to calculate the difference between 2 date fields, try this:

     

    Shipping Days =
DATEDIFF (
    Fact_SalesCogs[DeliveryDate],
    Fact_SalesCogs[ShippingDateConfirmed],
    DAY
)

     

     

    If you want to create a calculated table which has Shipping Days column, try this:

     

    New Table =
SUMMARIZE (
    Fact_SalesCogs,
    Fact_SalesCogs[SalesOrder],
    Fact_SalesCogs[DeliveryDate],
    Fact_SalesCogs[ShippingDateConfirmed],
    "Shipping Days",
        DATEDIFF (
            'Fact_SalesCogs'[DeliveryDate],
            Fact_SalesCogs[ShippingDateConfirmed],
            DAY
        )
)

     

     

    The pbix file I tested is here.

     

     

    Best Regards,

    Stephen Tao

     

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

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
3 years ago

Good morning

Is it possible to create a measure with the DATEDIFF form using two date fields within the source table?

I am trying to calculate an indicator for invoices with start and end date with reference to the accumulated of a column (consumption), which adds the previous consumption data and I must divide it by the number of days that arises from the invoice period. I have the column of number of days created with the DATTEDIF forula but I skip the error when creating the indicator.

I do not know if the error is that. that a measure is necessary.

Thank you very much in advance.

Greetings