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.

TomMartens's avatar
TomMartens
Icon for Super User rankSuper User
5 years ago

Hey ka047 ,

 

can you please explain why this won't work

Shipping Days =
SUMX(
Fact_SalesCogs,
Fact_SalesCogs[DeliveryDate] - Fact_SalesCogs[ShippingDateConfirmed]
)

Regards and a safe passing to 2021,

Tom 

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

 

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    5 years ago

    Hey ka047 ,

    what is your data source?

    What is the data type of both columns? Both should be either Date or Datetime?

    Did you import the data or are you using DirectQuery or LiveConnection?

     

    Tom