Problem of the Week #5 (DAX) - Dynamic Last N Weeks Trend

[quantumudit]

Hello Everyone,

Here goes my solution for POTW-05.

Can’t resist representing the solution as a dashboard

Thanks a lot, @MudassirAli & @BrianJ for this amazing challenge.

POTW05_DAX_Udit.pbix (435.8 KB)

I hope that I understood the requirements correctly.

The last date present in the COVID Data is 11th Jan 2021 which is Monday (Weekday-1) thus, I dynamically ignored this data and hence, the last week.

So, the last date in all of the analysis ends on 10th Jan 2021 (Sunday).

I guess this is right as I’ve compared my analysis with that shown in the picture (challenge page).

Similarly, I’ve some confusion on the requirements to calculate the trend percentage, particularly, calculating the prior N-weeks.

However, I moved with my gut feeling and found my answers matching with the snapshot of the solution shown on the problem page.

Well, Thanks a lot for the snapshot of the solution in the PBIX file.

Although now the video of the problem is available on the EDNA Youtube Channel, it would be great to launch both video and POTW at the same time, so that, there wouldn’t be any confusion.

Assuming my solution is correct, I am open to any feedback and can’t wait to see how others solved this puzzle

Also, want to know how we can show the trend with ONE keyboard Command in Power BI

[Rajesh]

@MudassirAli

Thanks for the great challenge.

Here is my solution

Summary

POTW - 5 - Dynamic Last N Weeks Trend - Rajesh.pbix (435.7 KB)

[Greg]

Hi All.

Here is my submission for POTW #5.

Summary

POTW #5 - Dynamic Last N Weeks Trend - Greg Philps.pbix (438.3 KB)
NOTES:

• set “today” to max date of dataset (Jan 11)
• moving average of total cases used to display smoother line charts
• used filter pane to select only Brazil, India, and US in line chart

ISSUES:
1: I can’t see the switch to set small multiple title to left of chart, so there underneath
2: on first try with MA line chart displays for every day, so too many points are displayed, and lines look stepped instead of smooth

[Sue]

Here is my solution for this challenge, very timely.
Thanks for the problem,
Sue

Summary

POTW - 5 - Dynamic Last N Weeks Trend @SueBayes.pbix (437.2 KB)image|690x394

[BrianJ]

@quantumudit,

Nice! Interesting approach on the solution, and bonus points for the attractive animated dashboard.

Sorry for the confusion on the requirements, but your assumptions were exactly right.

A word about the Problem of the Week intro videos. Originally, we’ve been posting these on YouTube concurrent with the posting of the problem on the forum. However, even with expanding the Enterprise DNA YouTube videos to every weekday this year, there’s still a lot of “competition” for those daily slots, with a lot of the experts now creating videos, Sam revisiting many of his earlier topics, the podcast video weekly, Data Challenge videos, POTW solutions, etc. Thus, to make room for all the other technical content we want to post on YouTube, we’ve decided to move the POTW intro videos to the forum header and the initial post only. However, we definitely will try to get those synced up so that the post introducing the problem and the video accompanying it are available at the same time.

Thanks for the feedback, and also for your enthusiasic participation in POTW.

• Brian

[MudassirAli]

@quantumudit Thanks for participating in the challenge. There was a delay between uploading the video and posting the challenge on the forum and we are really sorry for that. Moreover, I should have mentioned that it is just a DAX challenge and not a dashboard challenge

The dashboard literally looks very cool. Bars, Trend% highlighting & Dynamic small Number of weeks header, everything adds a very nice touch to the report. Now I am wondering whether you will participate in Challenge # 12 or not

Your DAX solution is equally amazing. It’s always a learning experience seeing other people’s approach towards solving the same problem.

Well done on this One !

[chrish]

Mudassir

Thanks for the challenge - and for the guidance on using 11 January as ‘today’ for the sake of the calculations.

As this is a dynamic solution I thought it best to publish the result to Power BI service - hope this link works.

https://app.powerbi.com/reportEmbed?reportId=e3a192b2-70a8-4b80-9020-ea7434456a9e&autoAuth=true&ctid=05ce1cbd-1d9d-44ba-8c1d-2ff97ee4b6ff&config=eyJjbHVzdGVyVXJsIjoiaHR0cHM6Ly93YWJpLW5vcnRoLWV1cm9wZS1yZWRpcmVjdC5hbmFseXNpcy53aW5kb3dzLm5ldC8ifQ%3D%3D

Of course you can’t see the DAX from that - so here is the PBI file:

eDNA POTW 5 - Chris H.pbix (497.7 KB)

Summary

My approach was to use the “Week offset” column from the date table to provide the weekly basis of the calculation.

As ‘today’ was actually 11 January 2021, I set a ‘Reference date’ measure to this date and a branched measure to calculate the number of weeks offset from actual today to that reference date. I thought about making the reference date dynamic as I can see some value in being able to run this analysis from alternative starting points - but didn’t get round to making this work.

The DAX for the last N weeks and the N weeks preceding those N weeks was not particularly tricky - just a CALCULATE with a FILTER on the weeks being referred to - the hard bit was trying to keep all the Ns straight!

Using Unicode characters in a measure was new to me. I found all of the Unicode characters on - https://unicode-table.com/en/ - but it took some trial and error to figure out which number I needed in the DAX expression (hint: not the Unicode number!)

Using small multiples tables was also a nice new tool to be introduced to - I never would have found it hidden in the Previews without it being suggested here - it could well have been useful for the COVID dashboard challenge. I added a fourth country to the Top N filter so as to avoid a blank corner of the visualisation.

All the best,

Chris

[jbressan]

This is my solution for Problem Of The Week # 5

The video helped me understand the problem better, thanks.

POTW - 5 - Dynamic Last N Weeks Trend - jbressan.pbix (453.7 KB)

[BrianJ]

Attached is my solution to Problem of the Week #5.

I’m getting a huge kick out of how people have started adding individual flair to their entries (@jbressan’s timeline, @quantumudit’s full dashboard, @Greg going Greg-style challenge entry, etc.). In keeping with the spirit, I used neither the UNICHAR() nor conditional formatting approach for the icons, instead going with custom flaticon.com icons hosted on imgbb.com, one of the techniques I discussed on last week’s members’ event with @sam.mckay.

Summary

POTW - 5 - Dynamic Last N Weeks Trend - @Brianj solution2.pbix (437.1 KB)

Keep up the great work everybody! And thanks to @MudassirAli for a terrific, multifaceted problem this week.

• Brian

[tweinzapfel]

Everyone,

Attached is my completion. Definitely enjoyed this one as I do find that working with time calculations can be somewhat challenging. Here is my solution:

Here is the file:

POTW #5 Final.pbix (436.5 KB)

[MudassirAli]

Hi All,

Here we are wrapping up the Problem of The Week # 5 with more than enough creative solutions to tackle the week-related calculations. I thank you all who participated in the challenge proving once again that there are many ways to tackle the same problem. I absolutely loved how the members presented the solution in a dashboard style format making these challenges even more exciting.

How I Approached the Problem?

If we had the data till the most recent date it could have been easier to tackle the week related calculation however, in practical life, it is not necessary that you have the data till the most recent date. For example, my office was closed in the last week of Christmas therefore, I didn’t have any sales or production data when I tried to calculate the Last Week production or Sales. To tackle this problem, I tried to restrict my weeks from the 1st day till the last date in which the data is available i.e. 11th Jan 2021 with the help of the following measure:

Week Offset Upper Limit = 
Var _MaxDate = MAX('COVID Data'[Date])
RETURN
 CALCULATE(
    MAX( 'Date'[WeekOffset] ) -1,
    'Date'[Date] <= _MaxDate
)

Then used this measure (set as variable called _UpperLimit) to get the range of weeks that has the data available till the last week:

CALCULATETABLE(
                VALUES('Date'[WeekOffset]), 
                'Date'[WeekOffset] <= _UpperLimit)

After that used this virtual table inside the TOPN function to get the Last N Weeks dynamically with Parameter selection :

TOPN(
            _WeekNumbersSelected,    //this is a variable for weeks selection SELECTEDVALUE('Weeks 
                                                          Selection'[Parameter])
            CALCULATETABLE(
                VALUES('Date'[WeekOffset]), 
                'Date'[WeekOffset] <= _UpperLimit),
            'Date'[WeekOffset], 
           DESC
)

Once I got the required number of weeks, I used the ADDCOLUMNS to get the total cases for the weeks selected:

Var _vTable =
 ADDCOLUMNS(
        _TopNWeeksSelection,
        "@Cases",
        [Total Cases]
    )

Finally, it was time to sum all the cases that are selected in the Parameter:

Var _LastNWeeksCases = 
SUMX(
    _vTable,
    [@Cases]
)

For the Weeks selected that are before the Last N Weeks, the only change made in the calculation was to take the Week Offset Upper Limit and subtract it from the whatever weeks are selected dynamically and the rest of the calculations were same as above:

Var _PreviousNWeekOfYear = [Week Offset Upper Limit] - 'Weeks Selection'[Number Of Weeks]

For the more detailed solution, you can check out My Video for the POTW 5 solution.

Conclusion

I hope you all liked this challenge and learned something from it as I literally learned a lot from the submissions. Thank you once again for being active and amazing members of ENTERPRISE DNA.

I think that is it from my side & attaching the PBIX file of my solution.

STAY TUNED FOR POTW 6!

POTW 5 Solution - Mudassir Ali.pbix (457.5 KB)

[sam.mckay]

Fantastic work on this everyone, love the concept and the efforts working through quite complex solutions

[quantumudit]

Thanks a ton…
Glad that you liked the mini-dashboard

Yes, I would definitely participate in dashboarding challenges That’s on my To-Do list.

[quantumudit]

Yeah, I can relate the busy schedule as I also go through it often… Its tough
Anyway, I’m amazed by the level of participation this week.
So much of learning…

Thanks a lot everyone for the different approaches …

[BrianJ]

POTW #5 Participants,

My apologies - in the flurry of new content we’ve been working on developing and rolling out, I have been remiss in my sticker payout responsibilities. I believe that everyone who submitted a solution for #5 already has been sent one, except for:

@MudassirAli
@Greg

However, if you’ve submitted a solution for any of the past POTWs and want me to send you one, just send me (brian.julius@enterprisedna.co) your physical mailing address (please, NOT your email address ) and I’ll drop one in the mail to you right away. Mine got the honored center spot on my laptop:

Thanks to all for participating. See you over in POTW #6…

• Brian

[quantumudit]

Excited about this cool sticker but, not yet received it… I got it for POTW-01, however, it might be taking a lot of time to travel to India

[BrianJ]

@quantumudit,

I sent that out weeks ago, so apparently the Postal Service has beamed that one into a parallel universe. I’ll drop another one in the mail to you this weekend.

– Brian

[JBocher]

Hi all,

Here is my submission to the problem :

POTW - 5 - JBocher.pbix (437.3 KB)

[MudassirAli]

@JBocher Really nice to see that you are jumping on solving these challenges. It will certainly help you in improving your DAX & PQ skills. It is always a great idea to look at different solutions provided by the participants & it is really interesting to see how different people solve the same problem with different methods.

Really great work with the solution. You can make the solution more dynamic by adding MAX(‘COVID Data’[Date]) or something else to make it dynamic instead of hard coding the Date as DATE( 2021,1,11 ) so the last date would be dynamically set in the DAX code.

Hope to see your entries in EDNA report challenges also.

[sam.mckay]

Love the eDNA sticker