Date discrepancy between "Next 90 Days" in slicer and Power Query code

[kstiegler]

I have a date table in a Power BI Dataflow, refreshed daily at 1 AM UTC, that includes a column with the following code excerpt:

Table.AddColumn(#"Inserted Next 30 Days Selector Sort", "Next 90 Days", each if [Date] < DateTime.Date(Date.AddDays(DateTime.LocalNow(),-90)) then "Before Last 90 Days" else if Date.IsInPreviousNDays([Date], 90) then "Last 90 Days" else if Date.IsInNextNDays([Date],90) or Date.IsInCurrentDay([Date]) then "Next 90 Days" else if [Date] > DateTime.Date(Date.AddDays(DateTime.LocalNow(),90)) then "After Next 90 Days" else "Error", type text)

In my report published in the Power BI service, I also have a relative date slicer. As of right now, 7/3/2024 11:47 AM CST, when I select "Next 90 Days" in the relative date slicer, the results go through only 9/30/2024, whereas the Power Query column "Next 90 Days" includes 10/1/2024. 

Why does this discrepancy exist? It seems like the M function Date.IsInNextNDays should yield the same results as a slicer that says "Next X Days," assuming they are looking at the same date table and column.

[Alican_C]

to ensure that both Power Query and Power BI Service use the same reference point for date calculations, you can adjust the Power Query code to explicitly use UTC time.

something like:

let
Source = ...,
InsertedNext30DaysSelectorSort = ...,
UtcNow = DateTimeZone.SwitchZone(DateTimeZone.UtcNow(), 0),
AddedNext90DaysColumn = Table.AddColumn(
InsertedNext30DaysSelectorSort,
"Next 90 Days",
each if [Date] < DateTime.Date(Date.AddDays(UtcNow, -90)) then "Before Last 90 Days"
else if Date.IsInPreviousNDays([Date], 90) then "Last 90 Days"
else if Date.IsInNextNDays([Date], 90) or Date.IsInCurrentDay([Date]) then "Next 90 Days"
else if [Date] > DateTime.Date(Date.AddDays(UtcNow, 90)) then "After Next 90 Days"
else "Error",
type text
)
in
AddedNext90DaysColumn

[v-yohua-msft]

Hi, @kstiegler 

The difference you see may be due to the different ways Power Query and Power BI slicers handle the end of the day.

In Power Query, the function treats the current day as part of the "next N days". So, if today is July 3, 2024, then the next 90 days will include July 3, 2024 to October 1, 2024 inclusive. Date.IsInNextNDays

On the other hand, Power BI slicers might consider "90 days in the future" as 90 days after today, so it will include July 4, 2024 through September 30, 2024.
This is a common problem when dealing with relative dates, and it's always important to understand how each function or feature calculates these dates

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.