Forum Discussion
Advocate Icountdistinct for each customer
I have data that looks like this:
I am struggling with writing a table that includes a line for each customer (by email), a column that include number of orders for in 2017 and a colum that counts 2018 orders.
Thanks,
Erez
I solved it with:
Orders2017 = CALCULATE(DISTINCTCOUNT('Table'[Order Number]), FILTER('Calendar', 'Calendar'[Year]=2017))
Orders2018 = CALCULATE(DISTINCTCOUNT('Table'[Order Number]), FILTER('Calendar', 'Calendar'[Year]=2018))
SEgggments = SUMMARIZE (
'Table',
'Table'[UpperEmail],
"NumberofOrders2017", [Orders2017],
"NumberofOrders2018", [Orders2018],
"NumberofOrderslast5months", [Orders in last 5 months] )
4 Replies
- LivioLanzo
Solution Sage
If you create two dimensions table one containing the unique list of emails and the other table being a usual calendar table then you just need to create a simple matrix
erezbenmosheThanks but I need it for further calculations so I need it as a table.
- LivioLanzo
- erezbenmoshe
I solved it with:
Orders2017 = CALCULATE(DISTINCTCOUNT('Table'[Order Number]), FILTER('Calendar', 'Calendar'[Year]=2017))
Orders2018 = CALCULATE(DISTINCTCOUNT('Table'[Order Number]), FILTER('Calendar', 'Calendar'[Year]=2018))
SEgggments = SUMMARIZE (
'Table',
'Table'[UpperEmail],
"NumberofOrders2017", [Orders2017],
"NumberofOrders2018", [Orders2018],
"NumberofOrderslast5months", [Orders in last 5 months] )
