This was a table calculations based challenge, and I always tackle these by building out the data required in a tabular format. The challenge was also reminiscent of a previous challenge Sean has set, which I’ve blogged about here, and admit I used as a reference myself.

So let’s get started.

To start with, we need the month date, the **Sub-Category**, the **Sales** value and the difference in Sales from the previous month. For the month date, I like to define this explicitly

**Order Date Month**

DATE(DATETRUNC(‘month’,[Order Date]))

This aligns all Order Dates to the 1st of the relevant month.

Add **Sales Category, Order Date Month **(set to discrete exact date blue pill), and **Sales** into a view, then set a **Quick Table Calculation** of **Difference** on the **Sales** pill

Edit the table calculation to compute by **Order Date Month** only, so the previous calculation restarts at each **Sub-Category.**

Then drag this pill from the marks card into the left hand data pane to ‘bake’ the calculated field into the data model. Name the field **Sales Diff. **The re-add **Sales** back into the view too, so you can double check the figures.

Identify whether there is an increase with the field

**Diff is +ve**

IF [Sales Diff]>0 THEN 1 ELSE 0 END

Add this into the view too, and verify the calculation is computing by **Order Date Month** only again.

Now we need to work out if the row matches the previous value

**Match Prev Value**

LOOKUP([Diff Is +ve],-1) = [Diff Is +ve]

The LOOKUP is looking at the previous row (identified by the -1) and comparing to the current. If they match then it returns True else False.

Again add into the view, and again double check the table calc settings. In this case there is **nested calculations** so you need to double check the settings against each calc referenced in the drop down

Now we need to work out when there are consecutive increases, and how many of them there are

**Increase Streak**

IF (NOT([Match Prev Value])) AND ([Diff Is +ve] = 1) THEN 1

ELSEIF [Diff Is +ve] = 1 THEN ([Diff Is +ve]+PREVIOUS_VALUE([Diff Is +ve]))

END

If the current row has a +ve difference and the previous row wasn’t +ve, then we’re at the start of an increase streak, so set to 1. Else, if the current row has a +ve difference then we must be on a consecutive increase, so add to the previous row, and this becomes a recursive calculation, so builds up the values..

Add this onto the view, set the table calc settings, and you can see how this is working…

So now we’ve identified the streaks in each **Sub-Category**, we just want the maximum value.

**Longest Streak**

WINDOW_MAX([Increase Streak])

Add this and set the table calc setting again. You’ll see the max value is spread across every row per **Sub-Category**.

Finally we need to identify **Sales** values in the months when the streak is at its highest.

**Sales of Month with Longest Streak**

IF [Longest Streak]=[Increase Streak] THEN SUM([Sales]) END

Add this into the view again (don’t forget those table calc settings), and you’ll notice that for some **Sub-Category**s there are multiple points with the same max streak

With all this we can now build the viz, which is relatively straight forward….

Add **Order Date Month **(exact date, continuous green pill) to **Columns**, **Sub-Category** to **Rows** and **Sales** to **Rows**. Edit the Sales axis to be independent, then change the line type of the **Path** to stepped

Add **Sales of Month with Longest Streak** to **Rows** and set to dual axis, and synchronise. Make sure the mark type of the 2nd axis is set to circle, and remove **Measure Names** from the **colour **shelf of both marks.

Manually set the colour of the line chart to grey. Add **Longest Streak **to the **Colour** shelf of the circle marks card. Adjust the colour to use the green palette, set to stepped of 5 value and ensure the range starts at 0 and ends at 5 (don’t forget to edit the table calc settings!).

Now add **Longest Streak** as a discrete blue pill to the view too.

This is all the core components. The last thing we need to do is sort the list. I wasn’t entirely sure how it had been sorted, apart from the largest **Longest Streak** at the top. I created a new field for this

**Sort**

[Longest Streak]*-1

and added this as a blue discrete pill in front of **Sub-Category**….

…, then hid the column.

Then just apply the tooltip and relevant formatting on the chart.

For the legend, I created a new field

**Legend**

CASE [Sub-Category]

WHEN ‘Art’ THEN 0

WHEN ‘Chairs’ THEN 1

WHEN ‘Labels’ THEN 2

WHEN ‘Paper’ THEN 3

WHEN ‘Phones’ THEN 4

ELSE 5 END

and added this into a new sheet as below

The components then just need to be added to the dashboard. My published version is here.

Happy vizzin’! Stay Safe!

*Donna*