Relative date filter showing blank value when report is uploaded to service

[analytical_mind]

Hey everyone,

I have developed a dashboard where I am able to separate "on time" orders (delivery date in the future) and "backlog" orders (delivery date in the past, and not yet fully delivered).

My fact table basically consists of:

Delivery date (in DD/MM/YYYY format)

Open Quantity (decimal number format)

Backlog flag (calculated column): if [open quantity] > 0 && [delivery date] < today () then "Backlog", else: "Ok"

Adjusted delivery date (calculated column: if [backlog flag] = "Backlog" then today (), else [delivery date]

I also do have a dimension table with all dates, which I use as my main date filter. The relationship is one to many (from dim_date[date] to fact_table[adjusted delivery date]).

I have created 3 measures to summarize these fields:

measure 1: calculate ( sum( [open quantity] )

measure 2: calculate ( sum( [open quantity] ), [backlog flag] = "Backlog" )

measure 3: calculate ( [measure 1] - [measure 2] )

Then I have created 2 cards in the dashboard containing each: measure 2 and measure 3

So here's the thing: on my dashboard, I'm using the relative date functionality as a filter (page filter, as seen in the screenshot below) and it works flawless while I'm using the app.

After I have uploaded the report to the pbi service, on d+1 (which translates to if I upload today, the problem will happen tomorrow) the card with measure 3 will show data and the card with measure 2 will show (Blank).

I expected both cards to show data until a new upload is made.

I also do think that the problem might be related to the Relative date filter, but I'm lacking knowledge here. Could you kindly help me out?

Thanks!

[collinq]

Hi @analytical_mind ,

Following your Relative Date train of thought....the Service is set to use the UTC time zone.  That may be just different enough from you to be causing your issues.  The best way around this is some DAX to get you to the right date/time that you need.

Here are some of the links that might be helpful:

Dynamic time zone conversion using Power BI – The White Pages (unlimitedviz.com)

Set local time zone Power BI Service - Microsoft Power BI Community

Solved: TimeZone - Microsoft Power BI Community

Solving DAX Time Zone Issue in Power BI - RADACAD

[analytical_mind]

Hi @collinq , thanks for your reply.

I wonder if it might also be related to how I've built my dim date table. I have a query named 'Today' which reads:

= DateTime.Date(DateTime.LocalNow())

I use this query as an input for my dim date table:

= List.Dates(Today, 1095, #duration(1, 0, 0, 0))

then I transform it to a table. This means that everytime I click the refresh button, it'll load 1095 days from the day I clicked.

[collinq · Accepted solution]

Hye @analytical_mind ,

That might be it....the LocalNow might be using the UTC Service time?  Perhaps use a DAX formula or use Query Editor to set the time to a specific time zone?

[analytical_mind]

I found a workaround setting up daily refreshes (in pbi service). It seems to be working now.