DATEDIFF DAX measure

[ka047]

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. 

[Syndicate_Admin]

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

[Anonymous]

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

[Anonymous · Accepted solution]

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.

[amitchandak]

@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-Differ...

[TomMartens]

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]

[TomMartens]

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

[MattAllington]

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))