cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper II

Sum/Count of the output from a if statement

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.

2 REPLIES 2
Community Champion

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)

Helper II

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!

Announcements

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors