cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Philthy Frequent Visitor
Frequent Visitor

April fools?

Ok - is someone at Microsoft having a giigle at my expense?

 

I work in health and have just received the new quarterly data for patients beginning April 1, 2018.  I've imported it into my warehouse using the exact same routine which imports the last 3 years (at the same time), but when I update my reports, almost everything disappears when filtering for 1 April 2018.  

 

It's WEIRD though.  I can create a simple bar chart and see volumes by quarter (so April 2018, Jan 2018, Oct 2017, etc.) and have a pie chart that does the same thing, and an item which shows the overall count, and a table showing the data.  When I click on ANY date except April in any of these visuals, everything filters accordingly (charts highlight my selection, overall count updates, etc.), but if I click April in ANY of them, it highlights NOTHING and my overall count turns to (blank).

 

I wondered if it was a FUTURE date causing issues.  So I did a bulk update on the table to March 1.  That fixed it.  Updated back to April....broken.  Started playing....1 Aug 2018 worked fine, so did tomorrow's date, so did 2 April.  But as soon as it was set to 1 April again....boom.

 

Wasted a day tracking this down.  No idea what to do.  Having to introduce a completely new derived field in my processing and apply to every dashboard JUST to allow me to update my reports.

 

Anyone?!?!

15 REPLIES 15
Community Support Team
Community Support Team

Re: April fools?

Hi @Philthy,

 

Are you sure there existing any data record on 1 April 2018 this specific day? You said, the bar chart shows volumes by quarter (so April 2018, Jan 2018, Oct 2017, etc.), it might show the values in a period, we cannot determine there existing data on any specific date.

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Philthy Frequent Visitor
Frequent Visitor

Re: April fools?

Thanks for the response Yuliana.

 

Yes - there is plenty of data in there for that date.  A simple SQL count by date against the source table (and it's a single table driving the dashboard, so no complexities around table joins to consider) shows tens of thousands of records.

 

As you'll be aware, when you include a filter in the filter panel, using "basic filtering", the number of records shows up next to each available selection.  Curiously, the correct number of records is displayed in this list, but selecting the April quarter blanks out every visualization!  But, if I very simply replace the filter with my new text-based version of the date, it works beautifully.  

 

It's inexplicable, and I have absolutely no clue if anyone is able to reproduce it.  Unfortunately, it's not easy for me to share the dash to demonstrate, as it contains sensitive data.

Captain Frequent Visitor
Frequent Visitor

Re: April fools?

I can confirm that we are also experiencing this issue.

I have noted that the issue seems to affect Slicers that are set to April 1st.

Not all kinds of date slicers seem to suffer the issue, however. I have noted that Dropdown and List do have the issue. Other types do not.

 

Notably, I do not experience the problem with setting the date by the Advanced Filter, but the Basic Filter displays nothing.

AfriKan Frequent Visitor
Frequent Visitor

Re: April fools?

I am having the same problem, across multiple reports.  I use week ending dates for most of my reports.  And the last week ended on 1 April 2018.  I can see the date in the data.  And I can see the date in reports.  But, if I try to filter (using a slicers) for that week - no data appears.

 

I also have data changing itself. A "End of Hour" (that matches a hour, and a date in other columns) is correct when viewed in Data, but adds a hour itself when viewed in Report.PowerBI 1.pngView in dataPowerBI 2.pngView in report

Philthy Frequent Visitor
Frequent Visitor

Re: April fools?

Well I'm pleased it isn't just me (though I'm sorry it's affecting you guys too!).

 

My reports are currently sourced from a single master table, which combines a myriad of stuff into an easily consumed data feed, so all I've done is add an extra field to the ETL process which delivers a text-based date instead of an "actual" date.  This works fine (a bit of a pain to have to replace everywhere, but didn't take too long).  A bonus to this method is not having to follow the PowerBI date types, so I can format the date however I like (e.g. something nice and short which doesn't take up lots of space).

Rakshya Frequent Visitor
Frequent Visitor

Re: April fools?

I have wasted two days just to fix with zero outcome, whenever I try to run the dashboard for 1st April 2018, it ignores and does not bring any data. I tried replacing the value with some other future dates, it works fine but does not work for Ist April.

I am trying to filter txn_date within a table, it filters for rest dates except 01/04/2018. 

Is it because of Day light saving causing this issue? This is impacting my BAU tasks, can anyone please assist?

image.pngimage.png

Regards,

Rakshya

finalWord Frequent Visitor
Frequent Visitor

Re: April fools?

We are experiencing the issue too. I have raised a support ticket with Microsoft. My colleague and I worked out that the issue does seem to be related to daylight saving time. I am curious as to whether all of you in this thread reside in countries who have changed their time on either Mar-31 or Apr-1. We're in New Zealand so for us Daylight Saving ended at 3am on Apr-1 with our clocks going back to 2am. 

 

If we have dates in our PBIX file (and the same in the service), when we format them as dateTime, there is obviously not a time component, but if you format that variable as datetime it shows the time as 12:00:00 AM. That is except for on April 1st, where it shows 1:00:00 AM for the time. I believe that this is what is causing the slicers that are built on dates not to work. And likewise the cross-highlighting as @Philthy explains.

 

I have created this very basic report to demo the issue: 

 

https://app.powerbi.com/view?r=eyJrIjoiYjJkODY0NjAtNGQ5Zi00NmM4LWJjOWUtODQxZGQwNGZiOGQwIiwidCI6ImRiZ...

 

On page 1, there is a table and a slicer containing the same date variable. If you select nothing on the slicer, then all three dates show in the table. And if you select Mar 31 or Apr 2, the table is appropriately filtered to these dates. But if you click Apr 1 in the slicer the table shows no data. 

 

On page 2, I've shown the same table but this time with the date formated as datetime (remember I didn't enter those times, I only entered dates, so this is what Power BI is automatically calculating). You can see that Apr 1 has a different time to the other two dates which I am sure must be related to the cause of this problem.

 

I will update here when I receive action on my support ticket - if the moderators don't do so first.

 

finalWord Frequent Visitor
Frequent Visitor

Re: April fools?

Update to report a workaround, of sorts. Microsoft support advised that changing your clock settings so that "automatically adjust for daylight savings time" is turned OFF will cause the report to work properly. I have tested this and in my case this is true. Maybe it will be for you guys as well. I've also stated that I don't consider this a solution as asking report consumers to fiddle with the clock settings is not a great option. Will keep this post updated as I hear more.

AfriKan Frequent Visitor
Frequent Visitor

Re: April fools?

Thanks, this worked for me.  Turned it to "OFF" ran my report, exported the results, and re-imported.  Not ideal, but at least I can carry on with other reports.