OK, here we go! Problem of the Week #5 is now live!
For background on this initiative, check out this post .
Unfortunately, DAX does not have any Time Intelligence function that can help in handling the week-related calculations easily. Therefore, different custom techniques can be used to handle the calculations over weeks. This challenge provides the opportunity to create custom DAX code(s) required for week-based calculations.
The model is fairly straight-forward that contains one Date table and one fact table related to the daily COVID cases by countries.
This week’s problem consists of the following requirements:
Calculate the Last N Weeks Covid cases excluding the current week. The numbers should change dynamically with the What-if-Parameters selection. The parameter starting from 2 and increasing incrementally by 1 till 28 weeks.
Calculate the Trend %. The trend % is calculated by comparing the Last N weeks with the N weeks prior to the Last N weeks Covid cases. For example, if week # 8 & week 9 are the Last N Weeks then it should be compared with the week # 6 & week # 7 dynamically.
Show the Trend Arrow with UNICHAR or Conditional Formatting. If the Covid cases are
Greater than 8% then the trend is upward,
Less than 0% then the trend is downward or
The trend is stable if is in between 0% and 8%.
The preferred solution would be to show the trend with UNICHAR Code rather than conditional formatting option. Do you know you can also show the trend with ONE keyboard Command in Power BI?
The last requirement is to dynamically show the Last N Weeks Covid cases with Line Chart using the Small Multiple Visual feature. Show by only the Top 3 countries with the most Covid cases & the country name should be at the bottom left side of the line charts.
We ask that if you post anything directly related to a solution to please use the “Hide Details” or “Blur Spoiler” options within your post. This will prevent people who have not yet completed their solution from seeing things they might not want to see yet and could influence their solution.
To use these options, just select the text or image you want to hide, click on the gear icon on the top right at the top of your post and select either Hide Details or Blur Spoiler.
Note that this is a DAX-only challenge, so no Power Query even if that is what you would choose to use outside of this challenge.
Eligibility for the Drawing
To encourage participation, we will be doing a drawing of five random participants each round to receive one of these cool Enterprise DNA vinyl laptop stickers:
To be eligible for the drawing, just send an email to email@example.com before 11:59pm ET, Wednesday, February 10, 2021 indicating you’ve successfully completed the challenge, and please provide your mailing address for us to send the sticker if you’re selected.
We hope you find this initiative fun, challenging and helpful in continuing to build your Power BI skills. All of the information you will need is contained in the files within this post. If you have any questions or comments, just message @BrianJ or @MudassirAli in this forum thread.
Good luck, and enjoy!!!
P.S. Your feedback is always welcome, and if you have a real-world problem that you think might make a good future Problem of the Week, please email it to us at firstname.lastname@example.org.
POTW - 5 - Dynamic Last N Weeks Trend.pbix (431.9 KB)