Reputation: 333
I have below Customer Transactions
data. In a month, customer may buy one or multiple times data pack ,or may not buy data pack.
Irrespective of how many times data purchased in a month by a customer. I'm trying to find number of months that each customer purchased the data.
Total Subscribed Months
is expected column.
Since customer may buy Data Subscribed (GB)
more than once in a month. First I'm calculating Total Data Purchased
in a month by customer.
Total Data Purchased = CALCULATE(
SUM('Customer Transactions'[Data Subscribed (GB)]),
ALLEXCEPT('Customer Transactions','Customer Transactions'[Account Number],'Customer Transactions'[Date])
)
Second, Calculate number of months customer purchased the data pack.
Total Subscribed Months =
CALCULATE(
DISTINCTCOUNT('Customer Transactions'[Account Number] ),
'Customer Transactions'[Total Data Purchased]>0
)
But its not working. Please advise how to correct formulae ?
Upvotes: 0
Views: 7914
Reputation: 1791
Assuming your table looks like this:
Account Number | CUSTOMER_TYPE | Data Subscribed(GB) | Free Data | Date | Total Subscribed Months |
---|---|---|---|---|---|
10001 | Retail | 250 | 0 | 01 October 2019 | 1 |
10001 | Retail | 0 | 100 | 01 November 2019 | 1 |
10002 | Retail | 200 | 0 | 01 October 2019 | 1 |
10002 | Retail | 250 | 0 | 01 November 2019 | 2 |
10003 | Retail | 300 | 0 | 01 October 2019 | 2 |
10003 | Retail | 0 | 0 | 01 October 2019 | 2 |
10003 | Retail | 100 | 0 | 01 October 2019 | 2 |
10003 | Retail | 100 | 0 | 01 November 2019 | 2 |
10003 | Retail | 100 | 0 | 01 November 2019 | 2 |
10003 | Retail | 0 | 0 | 01 December 2019 | 2 |
Total Subscribed Months =
CALCULATE (
DISTINCTCOUNT ( 'Customer Transactions'[Date] ),
FILTER ( ALL ( 'Customer Transactions'[Data Subscribed(GB)] ), [Data Subscribed(GB)] > 0 )
)
Total Data Purchased =
SUM('Customer Transactions'[Data Subscribed(GB)])
Table visual with Account Number
, Total Subscribed Months
and Total Data Purchased
.
Upvotes: 3