Forum Discussion
Date Different return wrong value
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!
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
9 Replies
- cengizhanarslan
Super User
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]) ) 
ronrsnfldI 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).
- 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
Resolver I
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.
v-achippaCommunity Support
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
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]))
