Date difference calculation

[Anonymous]

Hi,

I want the numbers of whole days difference between two dates, the required date and the transaction date. Both are in two different tables. The tables are joined by an ID on workorder ID, operation sequence and item ID. I tried to build a column based formula on DATEDIFF which is the following: Date_diff = [REQUIRED_DATE].[Date]-MIN([TRANSACTION_DATE].[Date]). Result is a number which I cannot interpretate. Of course first line should be 0, third line should be 1.

Any help is highly appreciated. Thanks in advance

[DOLEARY85]

Hi,

would the datediff function work istead of using minus between 2 dates?

DATEDIFF('Table1'[Date],min('Table2'[Date]),DAY)

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[Anonymous]

Hi Doleary85,

Power BI accepts formula, however Power BI does not end calculating. Does not show data. Is there an alternative?

[DOLEARY85]

Can you share a screenshot of what's happening?

[Anonymous]

Closed Power BI and tried again. I have a result but all figures high in 500 range. How can I adjust this? Comparance 25-Apr-23 to 25-Apr-23 should give a result of 0. Thanks for your help!

[DOLEARY85]

Might be a problem with the min transaction date - Create another column to see what the min transaction date is bringing through

[Anonymous]

Hi Doleary85,

Yes, this worked. Generated a minimum transaction calculation and this calculation is used in the Datediff. One question still left: how can I exclude days of the weekend in the date difference calculation, can you help me with that?

[DOLEARY85]

I think you'll need to do this a slightly different way to exclude weekends. 

There's a post here on how to do it: https://community.powerbi.com/t5/Desktop/DATEDIFF-excluding-weekends/m-p/947915

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[Ritaf1983]

Hi @Anonymous 

Is there a pbix you could attach?
From the picture, it's hard to tell what was done.

[Anonymous]

Data source is directly on our ERP system, import mode. How can I share?

[Ritaf1983 · Accepted solution]

 Just create an example with several rows of similar data