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.

MattAllington's avatar
MattAllington
Icon for Community Champion rankCommunity Champion
5 years ago

I assume you are writing a measure. Why won't SUMX work?  SUMX works just like a calculated column - I see no reason why it won't work. 

sumx(fact,datediff(Fact_SalesCogs[DeliveryDate], Fact_SalesCogs[ShippingDateConfirmed], day))