Forum Discussion

ka047's avatar
ka047
Helper 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 me calculate the difference, I would appreciate it. Thanks!

 

Shipping Days =
summarize (Fact_SalesCogs,
Fact_SalesCogs[SalesOrder],
Fact_SalesCogs[DeliveryDate],
Fact_SalesCogs[ShippingDateConfirmed],
calculate(
datediff(Fact_SalesCogs[DeliveryDate], Fact_SalesCogs[ShippingDateConfirmed], day)
 
The above isn't working for me and I know I am missing something. 
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.

8 Replies

  • MattAllington's avatar
    MattAllington
    Community 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))

  • TomMartens's avatar
    TomMartens
    Super 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 

      • TomMartens's avatar
        TomMartens
        Super 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

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    ka047 , Try like

     

    Shipping Days =
    Sumx(summarize (Fact_SalesCogs,
    Fact_SalesCogs[SalesOrder],
    Fact_SalesCogs[DeliveryDate],
    Fact_SalesCogs[ShippingDateConfirmed],
    "_1",
    calculate(
    datediff(min(Fact_SalesCogs[DeliveryDate]), max(Fact_SalesCogs[ShippingDateConfirmed]), day))), [_1])

     

    or

     

    Shipping Days =
    Sumx(summarize (Fact_SalesCogs,
    Fact_SalesCogs[SalesOrder],
    Fact_SalesCogs[DeliveryDate],
    Fact_SalesCogs[ShippingDateConfirmed],
    Fact_SalesCogs[DeliveryDate],
    Fact_SalesCogs[ShippingDateConfirmed])
    ,datediff(min(Fact_SalesCogs[DeliveryDate]), max(Fact_SalesCogs[ShippingDateConfirmed]), day))

     

    Also, refer: https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Difference-Across/ba-p/934397#M451

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi ka047 ,

    Could you tell me if your problem has been solved?
    If it is, kindly Accept it as the solution. More people will benefit from it.
    Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.


    Best Regards,
    Stephen Tao

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    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