Group by rows based on different columns

[dmzhou]

Hi Experts, 

I have a requirement which needs your advice

Assuming I have data below

Customer	createTime	closeTime
a	2018-09-11	2018-09-23
b	2018-09-11	2018-09-25

Expecting the result below

Time	CreatedCustomer	ClosedCustomer
2018-09-11	2	0
2018-09-23	0	1
2018-09-25	0	1

How can I achieve this?

[v-shex-msft · Accepted solution]

HI @dmzhou,

You can refer to below link to create new table with expand detail date, then use this table to summary and calculate on records between two date columns:

Spread revenue across period based on start and end date, slice and dase this using different dates

Regards,

Xiaoxin Sheng