CODEWITHSUNDEEP
reportingpowerbidax
McGinger
McGinger

Reputation: 29

PowerBI Track a list of Customers Over Time

I've got a pickle with a Power BI report. I have a set of Customers I want to track. This customer set was collected by filtering out on several columns on our start date; that is, they all started off matching the same criteria on a certain date.

Criteria were Num Licenses under 10k, %Adherence below .1, and Num Features >= 3.

In a Database, this would look like this:

╔═══════════════╦═══════════╦═════════════╦════════════╦═════════════╗
║ Customer Name ║ StartDate ║ NumLicenses ║ %Adherence ║ NumFeatures ║
╠═══════════════╬═══════════╬═════════════╬════════════╬═════════════╣
║ Customer A    ║ 2/21/2018 ║        6000 ║ .08        ║           5 ║
║ Customer B    ║ 2/21/2018 ║        4400 ║ .01        ║           4 ║
║ Customer C    ║ 2/21/2018 ║        2150 ║ .07        ║           4 ║
╚═══════════════╩═══════════╩═════════════╩════════════╩═════════════╝

I want to track this set of customers over time, so I want to see how this set of customers is doing the next week:

In a Database, this would look like this:

╔═══════════════╦═══════════╦═════════════╦════════════╦═════════════╗
║ Customer Name ║ StartDate ║ NumLicenses ║ %Adherence ║ NumFeatures ║
╠═══════════════╬═══════════╬═════════════╬════════════╬═════════════╣
║ Customer A    ║ 2/28/2018 ║        6000 ║ .11        ║           7 ║
║ Customer B    ║ 2/28/2018 ║        4400 ║ .01        ║           4 ║
║ Customer C    ║ 2/28/2018 ║        2150 ║ .07        ║           2 ║
╚═══════════════╩═══════════╩═════════════╩════════════╩═════════════╝

So, with my current criteria as filters in PowerBI, Customers A and C would not show up in reporting on week 2 because they no longer fit the criteria.

I do not have access to the database from where this data is being pulled; I can only update the report itself and none of the queries.

I am interested in being able to see how the set of customers from Week 1 is faring on week 2 (and week 3, and so forth) even though they no longer match the criteria in the filters. I am also interested in seeing the same information on new customers that join in later weeks with stats matching the initial criteria.

My issue here is that I'm not sure how to calculate a column that flags a customer if they matched criteria on a certain date, because this seems to filter all of the data to only that date.

Is what I'm asking clear, and is it possible?

Upvotes: 1

Views: 106

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

It's simple to set of a calculated column that check the criteria per row:

Criteria = IF(Customers[NumLicenses] < 10000 &&
              Customers[Adherence] < 0.1 &&
              Customers[NumFeatures] >= 3,
              "Meets", "Fails")

Given this column, you can create a new calculated column that checks whether the customer has ever met the criteria:

CriteriaEverMet =
    CALCULATE(MAX(Customers[Criteria]),
              ALLEXCEPT(Customers, Customers[CustomerName])) = "Meets"

Here's another formula that gives the same result:

CriteriaEverMet =
    "Meets" IN CALCULATETABLE(VALUES(Customers[Criteria]),
                              ALLEXCEPT(Customers, Customers[CustomerName]))

Upvotes: 1

Related Questions