It is more than just a tool for large enterprises ❗
Here I am, another year older or wiser 👴
However, this time around I was thinking about creating something special. Luckily, around the beginning of March, I had this idea of a Birthday Report in Power BI. More specifically, a report that tells me my age in years, months, weeks, and days.
Please find the final result below, and if you want you can read through the creative process itself.
Click on the full-screen button at the bottom right corner to enjoy the report in FULL-SCREEN mode.
Design
The original idea was just to show my age and record a video about that. However, I soon realized that nobody would be interested in that, right? 🤣
This meant that I have to come up with a design that can be used in an interactive report, where users can type in their D.O.B. and see a personalized report. Sounds much better ❕❗
After a couple of iteration I was happy with the background design:
Input parameters
The best way to interact with users without asking them to “move away” from the report is using What-If Parameters in Power BI. It is neat way to ask users to provide NUMERIC inputs and use them in our calculations or measures.
So this is what I did.
I created 3 What-If Parameters to pick up Year, Month and Day when user was born. And I placed those input fields over my balloons, clearly indicating which field is which.
Calculations / Measures
The only thing that I wasn’t 💯 sure was the best DAX option to easily calculate periods between. As I don’t have to use lots of date based calculations during my day job, I had to use the guide to find one that I can understand and use easily.
DATEDIFF function (DAX) – DAX | Microsoft Docs
Using the above link, I found DATEDIFF and it seemed like a good option. I was also impressed that with a single formula I can calculate age in years, months, weeks and days by only changing the last bit.
It’s called being efficient! 😎
Please find below the Years Alive measure, as this is the only one with a bit of a twist:
SWITCH (
TRUE (),
AND (
MONTH ( TODAY () ) >= ‘Month'[Month Value],
DAY ( TODAY () ) >= ‘Day'[Day Value]
),
//This bit is needed to make sure that Datediff YEAR takes into consideration actual BirthDay, not just BirthYear
DATEDIFF (
[Date Born],
TODAY (),
YEAR
),
DATEDIFF ( [Date Born], TODAY (), YEAR ) – 1
)
After that I just had to run a few more mainly text-based measures to indicate in the report age, next birthday, and just for fun an emoji based on user’s input.
Here are the DAX for those:
IF (
DATEDIFF (
TODAY (),
DATE ( “2021”, ‘Month'[Month Value], ‘Day'[Day Value] ),
DAY
) < 0,
365
+ DATEDIFF (
TODAY (),
DATE ( “2021”, ‘Month'[Month Value], ‘Day'[Day Value] ),
DAY
),
//In case Bday is prior to TODAY()
DATEDIFF (
TODAY (),
DATE ( “2021”, ‘Month'[Month Value], ‘Day'[Day Value] ),
DAY
)
)
SWITCH (
TRUE (),
[Days till next BDay] = 0, “
Happy Birthday 
“,CONCATENATE (
CONCATENATE ( “You have to wait “, [Days till next BDay] ),
” more days until your next Birthday
“)
)
SWITCH (
TRUE (),
[Years Alive] <= 30, “
“,[Years Alive] <= 60, “
“,“
“)
Final remarks
As a last step I just had to change Fonts, hide visual headers and just in general tidy-up the report itself.
For some reason, that’s the part I hate the most… I always forget one or two visual headers and then I have to go back to report, adjust, etc…
Anyways…
If you want to see the whole process in more detail, make sure to watch the video 👇 below.
Roland
Subscribe below 👇 to get full access to BI-Lingual Analytics Materials
💰 It’s free 💰
🙏Thanks for watching and if you liked this video please hit the 👍 button.
📢 Also be sure to subscribe to the channel so you never miss a new video.
0 Comments