Power BI/DAX-Count the occurences of spikes in daily sales data

Problem

Consider a sales dataset which records the daily sales logged by every sales person. The columns are:

  1. userid Unique id of the salesman
  2. date The date of the sales transaction
  3. sales The amount of sales

The bar chart of the daily sales per participant would appear as follows:

Daily sales for John

john

Daily sales for Jane

jane

We can easily notice that there is a spike in sales for both John and Jane In this article we will arrive at a way to determine these "spikes" and also count their occurrences.

What is a spike?

A spike can be defined as an abnormal jump in daily sales. We could define our own threshold. E.g. A daily jump of 10 or above.

Problem - Show the count of spikes for every sales person

We want to produce a table like the one below:
count of spikes
Also, show me a tooltip displaying the dates of the spikes.

Approach

Consider sliding a 2 day wide kernel across the following bar chart across each of the days. In each position compute the difference between the sales on current day and the sales on previous day. We will consider a spike if the Δchange is is greater than a certain threshold, say 10.

Solution

Step 1-Write a measure IsCurrentDayASpike to calculate if any 2 consecutive days of data for an user have a spike 

IsCurrentDayASpike = 
//if delta change in sales exceeds this value, then we consider a spike
VAR THRESHOLD=10
var userid=SELECTEDVALUE(Sales[userid])
var currentDate = SELECTEDVALUE(Sales[date])
VAR prevDate=Sales[Yesterday]

VAR salesOnCurentDateFilter=FILTER(ALL(Sales), Sales[date]=currentDate && Sales[userid]=userid)
VAR salesPreviousDateFilter=FILTER(ALL(Sales), Sales[date]=prevDate && Sales[userid]=userid)
var salesOnCurrentDate = CALCULATE(MAX(Sales[sales]),salesOnCurentDateFilter)
var salesOnPreviousDate = CALCULATE(MAX(Sales[sales]),salesPreviousDateFilter)

var isSpike = IF((salesOnCurrentDate-salesOnPreviousDate)>THRESHOLD, TRUE(), FALSE())
var spike=  IF(ISBLANK(salesOnPreviousDate), BLANK(), isSpike)

return spike

As we can see,the measure IsCurrentDayASpike relies on another measure Yesterday to calculate yesterday's date.

Yesterday measure

The code for the measure Yesterday is below. Unfortunately, DAX does not provide a easy way to compute relative dates using a single date value. The out of box DAX measure DATEADD is designed to operate on a dates table. Therefore, I am taking a more straightforward approach as shown below 

Yesterday = 
Var _SelectedDay = SELECTEDVALUE(Sales[date])
Var _PreviousDay = _SelectedDay - 1
VAR _Result =
IF(
    HASONEVALUE(Sales[date]), _PreviousDay
)
Return _Result

Step 2-Display a table visual to indicate which days had a spike

Step 3-Write a measure CountOfSpikes to count all occurrences of IsCurrentDayASpike for given user

This measure will aggregate all rows per user and filter on the measure IsCurrentDayASpike

CountOfSpikes = 

VAR userid= SELECTEDVALUE(SalesPersons[userid])
VAR filterOnSpikes=FILTER(ALL(Sales), SalesPersons[IsCurrentDayASpike]=TRUE() && Sales[userid]=userid)
VAR countOfRows = COUNTROWS(filterOnSpikes)
return IF(ISBLANK(countOfRows),0,countOfRows)

Step 4-Create a table Visual with the userid and COUNTSPIKES

count of spikes

Accompanying files

Github

https://github.com/sdg002/sdg002.github.io/tree/master/dax-find-spikes-daily-sales

Sales.pbix

The Power BI with worked out measures and visuals

DummyData.xlsx

The Excel which drives the Power BI