Hi everyone!
I am trying to create a card to show the number of active customers. Active customers are those whose the sum of revenue and the sum of quantity are more than 0.
Practically, it means to exclude those customers whose the sum of revenue and the sum of quantity are less than or equal than zero.
The reason behind that logic is that a customer could make an order and then return it some days later. Since in the data Source both those transactions are recorded, there will be a date in which the revenue and the quantity are positive, and a later date in which the same amount of revenue and quantity will be negative, leading to 0 the sum of those two metrics, for that specific customer.
At this purpose, I'm using a dataflow; I've created a list of those customers whose respect the above conditions in all their lifetime. Unfortunately, this approach does not fully answer to the request, because we would want to exclude the inactive customers within the selected period in the card.
Let me make an example: if a customer orders a product on the 28th of September and returns it on the 3rd of October, selecting as a date range in the card the only month of September, this customer should be considered active (because he has not done the return yet).
For the same reason, if the users selects in the card September + October in the date range, the same customer should be considered inactive, because the sum of revenue and quantity would be 0.
In other words, we should find a way to recalculate the transformation of the dataflow based on the specific time range selected in the final card.
Do you have any suggestion to do this?
Many Thanks!
Roberto
