Sum/Count of the output from a if statement

[MariusT]

Hi all,

I have created a report that contain sales, where I also have tried to include some low-level customer retention analysis.

Intro:

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 =

Calculate(Quantity,

Datesinperiod(

                        'Date'[Date],

                        Lastdate('Date'[Date]),

                        -1*[Period Value],

                        MONTH)

)

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.

[speedramps]

Hi Marius

Please consider this solution and leave kudos.

Try use it in a stack chart column with a monthyear x-axis.

For each bar in the chart, the measure will compare the 'current' bar with the future bars.

If a customer has sales in a period but not any afterwards, then it must have been lost in that period. 🙂

This is quicker and simpler than checking quatities.

Number of lost customers=

VAR maxdate = MAX('Calendar'[Date])

VAR currentcustomers=
VALUES ( FactSales[CustomerID])

VAR futurecustomers=
CALCULATETABLE (
VALUES ( FactSales[CustomerID]),
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Date] > maxdate))

VAR lostcustomers =
EXCEPT(currentcustomers,futurecustomers)

RETURN
COUNTROWS(lostcustomers)

[MariusT]

Hi @speedramps ,

Although it did not solve my specific issue, you did provide me with an alternative solution that by the looks of it might be just as good. Thank you!