I have created a report that contain sales, where I also have tried to include some low-level customer retention analysis.
1)First, I have a measure for the sum of sales quantity.
Quantity = sum('Sales'[Qty])
2)Then I have created a series from 1-12, using "New parameter", that can be used in a slicer to filter until 12 months back from todays date.
Period = Generateseries(1,12,1)
3)Then I have created a measure for the sum of sales quantity dependent on the chosen value in the slicer mentioned above.
Quantity Last Period =
Where the measure Period Value is calcualated: Period Value = Selectedvalue('Period'[Period],3)
4)Then, if there is a value for sales in the chosen period, a measure is returning 0, if there is a blank value, it returns 1.
Lost Customer = if(Quantity Last Period,0,1)
So, to the issue:
The measure in (4) return either a 0 or a 1 depending on the customer is lost or not. But, since the measure contain an if statement, the calculation is also evaluated for the total, where the value for Quantity Last Period is never blank, thus as a total, there are no lost customers. What I would like is to see the sum/count of lost customers for each period chosen in the slicer. Whether or not this is shown in the table or in a card is not important, I just want to see total lost customers.
The measure is shown in a table, where the customer number is located at the left most column, and then the measures for Quantity, Qyantity Last Period, and Lost Customers is shown.
I have tried to look through the forum for answers on this, but either I do not understand, or I have not managed to find a relevant post for this.
Any help is highly appreciated.