Distinct count based on multiple measures

[QlaroOne]

Hi all,

I have an invoice table with following columns:

- Customer

- Date

- Revenue

Customers can have multiple invoices per day, so multiple rows per customer and per date.

I need a report where I have following measures filtered by date:

- New customers: distinct customers who had a revenue of 0 in the years before selected dates year, but have a revenue of > 0 in selected dates year
(f.e. if selected "15th of november 2018" and customer has been invoiced on 1th of jan 2018 (no credit note) AND he has also invoiced in 2017, but got a credit note for that entire invoice in 2017=> it's a new customer on selected dates year)

- Ex customers= distinct customers who had a revenue of 0 in year of selection and the year before, but had a revenue > 0 in the year-2 before (before 1th of januari of selected dates year -1)
(f.e. we select 15th of november 2018. Customer has revenue 31th of december 2016, but not afterwards > ex customer. If he has revenue on 1th of januari 2017 > should not be an ex customer)

- Recurring customers = distinct customers who have had revenue in the selected dates year or previous year. So at least 1th of januari of previous year we had revenue for this customer or after that.

Of course I have a date table and a customer table linked to this invoive table...

Can anyone help us with this seemingly simple problem please?

Thanks a lot

[Anonymous · Accepted solution]

@QlaroOne,

Please note that there is no relationship between date table and invoice table. You can create the following measures in the invoice table. For more details, please review attached PBIX file. 

selectedyear = YEAR(MAX('Date'[Date]))

selected = var selectedyearvalue=CALCULATE(SUM(invoice[Revenue]),FILTER(invoice,YEAR(invoice[Date])=[selectedyear])) return IF(ISBLANK(selectedyearvalue),0,selectedyearvalue)

previousyear = var selectedyearvalue=CALCULATE(SUM(invoice[Revenue]),FILTER(invoice,YEAR(invoice[Date])=[selectedyear]-1)) return IF(ISBLANK(selectedyearvalue),0,selectedyearvalue)

year-2 and before = var selectedyearvalue=CALCULATE(SUM(invoice[Revenue]),FILTER(invoice,YEAR(invoice[Date])<=[selectedyear]-2)) return IF(ISBLANK(selectedyearvalue),0,selectedyearvalue)

new = IF(AND([selected]>0,AND([previousyear]=0,[year-2 and before]=0)),1,0) 

existing = IF([year-2 and before]>0&&[selected]=0&&[previousyear]=0,1,0)

recur = IF([selected]>0 &&[previousyear]>0,1,0)

new count = SUMX(VALUES(invoice[Customer]),[new])

existing count = SUMX(VALUES(invoice[Customer]),[existing])

recur count = SUMX(VALUES(invoice[Customer]),[recur])

Regards,
Lydia

[QlaroOne]

Hi v-yuezhe-msft

Thanks a lot for your response. Is there a way to do it with the relationship between dates en invoice table? Because I need this relationship for other measures/reports...

Thanks a lot!

[Anonymous]

@QlaroOne,

You would need to create another date table and create relationship between the date table invoice table for other measures.

Regards,
Lydia