Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I'm relatively new to Power BI so hopefully this is something super easy and obvious to the more experienced.
I have a basic report that contains a single line chart visual. The data source is a SharePoint list that contains a couple of columns, one of which is a date and the other (Title) is a number.
My visual simply plots how the number changes over time, with a new item being added to the SP every day and the dataset refreshed via a Logic App.
The date column is obviously UTC because it's a SharePoint list, so in Power BI I added a new column like this:
NZ Date = FORMAT('Stale Tickets Counter'[ReportDate]+TIME(13,0,0),"dddd dd/mm")
The data looks like this:
And the visual looks like this:
Ok, all good so far, so I publish to the Power BI service and everything is good.
..Until, the next time a new item is added to the SP list and the dataset is refershed via the Logic App.
The first item in the list, which has a date of 12th Jan UTC, converted to 13th Jan NZST by adding 13 hours, has become the 2nd of December, and been sorted to the end of the chart.
If I look at the data from the model in Power BI service:
Only that first one, the 12th Jan UTC has been interpreted as US date format by switching the day and month round then adding 13 hours, but the rest haven't.
Now, if I go back to Power BI Desktop, click refresh to include the latest data, then, without touching the model at all, re-plublish to the PBI service, the problem goes away.. until tomorrow!
Any insights on this would be greatly appreciated!
Solved! Go to Solution.
@GilbertQ It's been running a few days now with no problem with the date reoccurring. Thanks for your help!
@GilbertQ Thanks for your reply. I'm still very much unaware of what the various use cases and merits are of power query vs DAX. There seems to a lot that can be done in both equally.
Anyway, because I want to format the date I did this:
DateTime.ToText( [ReportDate] + #duration(0,13,0,0),"dddd dd/MM"))
I wasn't sure whether I would be better adding the column in query editor like you describe and then adding another in the report to format the date, but I chose this way for now.
I've also changed the sort column to the ID of the SharePoint list item so the visual will always be in chronological order even if the date gets mucked up.
I'll find out tomorrow when the refresh happens if this has worked. Thanks again.
User | Count |
---|---|
65 | |
27 | |
25 | |
17 | |
11 |