Happy January data friends!
If you’re like most of us out here, when you’re learning to use a new application and don’t know an elegant way to do something, you get it done by whatever means necessary. This can mean fun things like hard coding daylight savings dates knowing full well they change each year. *Raises hand*
“I’m working smarter”, you tell yourself. “It would take me another hour to learn to fix/test this when I can take 2 seconds in January and just move the dates around”. And you’re not wrong about that part. WHY FOR THE LOVE did we choose the first Sunday after a month with an irregular extra day every four years to have some parts of some countries shift one hour for awhile?
So you happily shift that job off to “future you” until you are ringing in the new year with 27 vague calendar reminders on January 1st to ‘Update that thing for the new year’ that you set for yourself 8 months ago.
Whelp, because you’ve been avoiding it and likely need a refresher on stuff that could break your PowerBI reports in January, here’s a collection of timely hints (mostly because somewhere something I built while learning is now broken).
January is showing before December
You’re minding your own business when someone says your report is showing January before December. You scoff, because you’re super sure your field is a date data type and that would just be bonkers. But then, you click on your report and nearly shoot diet coke out of your nose to see…….
You are probably doing this because you aren’t using the drill down buttons and instead just deleting those parts of your date column out of the visuals pane. To fix it you can add the date field back in again. Here’s a Demo.
My guess is you were doing it so you didn’t have to use the drill down feature. It’s super easy to use once you get the hang of it. It’s such a common issue for my users that I put it on the help page of most of my reports.
Here’s an example of one of the help pages I have:
See? You’re not the only one.
Multiple years of data getting lumped together by month
Ok so here’s the tricky thing. Once you found one report like that, there are probably other reports with that same problem, except that if you include data that spans multiple years, you’re probably not even going to notice it if you’re looking for out of order months.
Here’s two years worth of sales data, by units sold. See how in the Axis box, the Date field still has it’s year component? You can see December 2016 didn’t do so great, but December 2017 was amazeballs for Giant Lobster Claw Mittens sales.
If we take the year component out, it’s going to combine all of the January data, and put it together, all the February data and put it together, etc. Now December looks good, but you’re wondering why people are buying tons of mittens in May.
This might catch you off guard if you have reports that you have continually getting new data, probably on scheduled refresh, and didn’t think about multi year scenarios when you were building it.
In conclusion- go check your reports and fix them so they’ll work for multiple years. Or go turn on the recurring option on those January 1st calendar reminders.
Do you have any other January related things break? Let me know!
Con Mucho Am(ad)or