This tutorial will discuss and solve Problem Of The Week #5 in the Enterprise DNA forum using the DAX **TOPN** function. The problem requires a solution with 4 requirements. One requirement of the problem is to calculate the dynamic Last N Weeks Trends of a COVID-19 data report. **You can watch the full video of this tutorial at the bottom of this blog.**

## Abstract Of The Problem Of The Week #5

This is the laid-out instruction for Problem Of The Week #5:

There’s an available video in the Enterprise DNA Forum that you can watch about the in-depth requirement of the problem.

**Enterprise DNA** Expert Greg Philips also posted a solution that lets you see the range that you’re filtering in the report.

A member, *jbressan*, posted his solution with dates in a timeline format.

Enterprise DNA Expert Brian Julius also posted his solution where you can see the trend of the data.

## COVID-19 Data Table

This is the dataset provided in the Enterprise DNA Forum containing the number of COVID-19 cases from the 1^{st} until the 11^{th} of January 2021.

However, you can see that the Date table goes up until the 31^{st} of December 2021. To fix that, make use of the **WeekOffset** which is present in the Date table.

You can see that January 1, 2020 has the least number of WeekOffset while December 31, 2021 has the highest. From the given data, you need to calculate the last N weeks trend excluding the current week.

The current week is January 11, 2021 and it has a WeekOffset of -3. So, you need to get the number of cases before that WeekOffset or before the week of January 11.

## Week Offset Upper Limit Measure

The problem above indicates that you need to get the data from -4 until the mentioned criteria in the report. For the first requirement, set up the maximum date limit. Use the **MAX **function to get the maximum date of the COVID-19 cases in the dataset.

If you put the measure in a card visualization. You’ll see that the maximum date is January 11, 2021.

Since the current week is excluded you need to add more code to the measure. Input the **CALCULATE **function and set its arguments.

For the first argument, subtract 1 from the maximum date. For the second argument, write a condition that the Date from the Date table should be less than or equal to the maximum date. If the condition is met, the **MAX **function will subtract 1 from the WeekOffset number of January 11.

If you put the measure in a card visualization, you’ll see that the Week Offset Upper Limit is -4.

## Last N Weeks Cases Measure With DAX TOPN

The next thing to do is calculate the **Last N Weeks Cases**. The cases should be dynamic according to the week selection in the parameter box. This is the **Last N Weeks Cases** measure.

### Variables Inside The Measure

First, create the variables. The first variable in the measure is _**WeekNumbersSelected**. It uses the **SELECTEDVALUE **function to select the Weeks Selection Parameter that’s created using a What-If Parameter. The main reason for using the function is that there are no relationships connected with the Weeks Selection table in the data model.

The next variable, **_UpperLimit**, is simply the Week Offset Upper Limit. The **_MaxDate **variable is the maximum date in the COVID Date table. Next, the **_CurrentDate **variable is the selected value from the Date column of the Date table.

## DAX TOPN And CALCULATETABLE Functions

Then, limit the week offsets by using the **CALCULATETABLE **function. To best explain the rest of the codes in the measure, DAX Studio is used because it’s best in creating virtual tables.

If you copy the **CALCULATETABLE **syntax, paste it in DAX Studio and change the last condition to **-4**.

You’ll see that the results will give you the week offsets from -57 to -4.

From there, calculate the N weeks offset by using the **TOPN **function. Next, set the Date WeekOffset order to descending. If you want to go back four weeks prior, input **4 **after the **TOPN **function and run the code.

You’ll then see the week offset going from -4 to -7.

## ADDCOLUMNS Function Over DAX TOPN

To show the number of cases for those weeks, use the **ADDCOLUMNS **function. Next, create a virtual table named **@Cases **for the number of cases and bring the Total Cases as a virtual column.

After you run the code, you’ll see the number of cases beside each week in the results.

After that, get the sum of the cases’ results using the **SUMX** function. The function iterates all week offset found in the virtual table and sums all the number of cases.

The next variable is the **_LastNWeeksCases**. It dynamically shows the last number of cases. If you go to the data report and select a week in the slicer, you’ll see that the data inside the table changes based on the week-selection slicer.

## Weeks Before Last N Weeks Cases Measure With DAX TOPN

The next step is to calculate **Weeks Before Last N Weeks Cases**. Create a measure and copy the **Last N Weeks Cases **measure. Then, change the **_UpperLimit **variable name with **_PreviousNWeekOfYear **and subtract the **Week Offset Upper Limit** with **Weeks Selection Number Of Weeks**. Lastly, input **_Result **in the **RETURN **function.

## New Trend Percentage Measure

After that, you need to calculate the trend percentage and the new trend. This is the **New Trend % **measure. It uses the **DIVIDE **function to divide the **Last N Weeks Cases **to the **Weeks Before Last N Weeks Cases**. The measure then subtracts the quotient with 1.

You’ll then see the trend percentage in the table.

The next requirement is to show the trend using arrows. This is the **New Trend **measure, which uses the **IF** function.

So if the new trend percentage is greater than 8%, an arrow up will show indicating that the number of COVID-19 cases is rising. If the percentage is less than 0, an arrow down will appear indicating a drop in the cases. But if the percentage is between 0% and 8%, an arrow right will appear indicating that the cases are stable.

If you want to see what’s happening with the **Weeks Before Last N Weeks Cases** measure, input **_PreviousNWeekOfYear **in the **RETURN **function.

Then, create a card visualization and place the measure. You’ll then see -8 in the card visual. The value on the card changes depending on the week-selection slicer. Since the selection is 4, the value in the card is -8.

If you select 2 in the slicer, the card will show -6**.**

## Small Multiple Visualizations Feature

Another requirement of the problem is to show the results using small multiple visual features where you can add, position, and align multiple titles.

However, even after getting correct results in the table visuals, there’s a problem with the small multiple visual. If you bring Date from the Date table to the Tooltips section of the visualization, you’ll see that the visual will show dates beyond January 11, 2021.

The **_LimitDate **variable in the **Last N Weeks Cases **measure was created for this problem. It uses the **ISONORAFTER** function to get the results on or after January 11, 2021.

The variable was then used in the **_Result **variable inside an **IF **function condition.

So if the date is on or after January 11, the results will yield blank. Otherwise, the **Last N Weeks Cases **will be provided. If you input the **_Result **variable after **RETURN**, you’ll see that the visualization is fixed.

### Filters Pane Option

Another way of fixing the issue is to go to the Filters pane and bring Date from the Date table to the **Filter On This Visual** field.

Set the Filter type to **Advanced Filtering **and the **Show items when the value **section to **is on or before**. Lastly, set the date to January 11, 2021 and click Apply filter.

After that, the problem with the visualization will be solved.

******* Related Links ***** TOPN DAX To Showcase Top Customers This Period Vs Last PeriodWhen To Use TOPN In Power BI – A DAX Formula ReviewShowing The Last Three Customer Sales Using The TOPN Function**

## Conclusion

Accomplishing the tasks for this particular **Problem Of The Week** is possible with the use of DAX functions and visualizations. You can also use DAX Studio to create virtual tables and study their results before you use DAX functions like TOPN in your Power BI measures. Practice and study DAX to improve your data development skills.

All the best!

Mudassir

[youtube https://https://www.youtube.com/watch?v=sGZP2SvNcQI&w=784&h=441]

## Be the first to comment