Date Different return wrong value

[90_eryka]

Dear expert,

I have two tables (Table A: Invoice Data and Table B:GR Data). In Table B, i have group PO number with Max GR Date meanwhile in Table A i have merge it with Table B using PO number. However, when i want calculate Date different between Document Date and Max GR date. Its return wrong value. Both field are in data type format : date. Here is the query :

Day Diff = = if [GR Date] = null or [Doc Date] = null
                     then null
                     else Duration.Days([GR Date] - [Doc Date])

The result 

Doc Date	GR Date	Day Diff
02/19/2025	02/24/2026	42550
03/19/2025	02/24/2026	40698
07/31/2025	04/07/2023	-23688
10/24/2025	08/27/2025	-21634
09/13/2025	09/18/2025	5
01/06/2025	02/05/2025	30
03/03/2026	03/03/2026	0

Can anyone assist how to debug this issue?

Thank you so much!

[v-achippa]

Hi @90_eryka,

Thank you for the response and confirming that the issue is resolved now. Thank you for being part of Microsoft Fabric Community.

Thanks and regards,

Anjan Kumar Chippa

[90_eryka · Accepted solution]

Hi @v-achippa @vojtechsima @ronrsnfld @ralf_anton @cengizhanarslan ,

Thank you for your assistant and support.

I'm able to populate the Day Diff correctly with your guide. Here is what i did:

1. Transform both Doc Date and GR Date column as some of records may contain text

= Table.TransformColumns(
#"Reordered Columns",
{
       {"GR Date", each
                if _ = null then
                       null
               else
         let
             t = Text.From(_),
                   parts = Text.Split(t,"/")
             in
                  #date(
                  Number.FromText(parts{2}),
                  Number.FromText(parts{0}),
                  Number.FromText(parts{1})
                   ),
                       type date}
                          }
                          )

2. Day Diff

= Table.AddColumn(Custom3, "Day Diff", each if [GR Date] = null or [Document Date] = null
then null
else Duration.Days([GR Date] - [Document Date]))

TQVM

[ronrsnfld]

Your "if x=null" is unneccessary. If either a or b is null, then a-b => null and Duration.Days(null) => null.

So the following is equivalent, simpler and probably more efficient:

=Table.AddColumn(Custom3, "Day Diff", each Duration.Days([GR Date]-[Document Date]))

[v-achippa]

Hi @90_eryka,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @cengizhanarslan, @ralf_anton, @ronrsnfld and @vojtechsima for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[vojtechsima]

Hey, @90_eryka , I would make sure, you're using US format, so first, change your types with Culture with it, like this:

= Table.TransformColumnTypes(Source,{{"Doc Date", type date}, {"GR Date", type date}}, "en-US")

Then, when I replicated your code, I didn't have issues.

I would also advice rewriting the code like this:

Duration.Days([GR Date] - [Doc Date] )

The function itself will handle nulls for you, so you don't introduce extra logic on top of it.

Because you're basically doin:

[GR Date] - [Doc Date]

By default, Nulls works in a way that, for example, when you add or subtract anything, if there's null present anywhere, the result is always null.

So nulls are given, now this result give you Duration type, so you use the Duration function to just extract the Days from it.

[ralf_anton]

Hi,

Dein Problem liegt vermutlich in den unterschiedlichen Schreibweisen der Spaltennamen. 

GR-Date mit Bindestrich und Doc Date ohne Bindstrich aber mit Leerzeichen. Das erfordert eine differnzierte Kennzeichnung der Spaltenaufrufe:

if [Doc Date]=null or [#"GR-Date"] = null then
null
else
Duration.Days( [#"GR-Date"]-[Doc Date]))

[ronrsnfld]

The OP must have edited his file as what I've been seeing for the past 1/2 day shows GR Date, not GR-Date.

[ronrsnfld]

I cannot reproduce your problem with the information you have provided:

Must be something else going on.

Perhaps a link to a file that will reproduce your problem might be useful (with confidential information removed or obfuscated).

[cengizhanarslan]

Could you try:

Day Diff =
if [GR Date] is null or [Doc Date] is null then
    null
else
    Duration.Days( Date.From([GR Date]) - Date.From([Doc Date]) )