Churn: Combining subscription dates from two data sources

[mikkelakabel]

Needs churn rates over time from two data sources.

I have :

id	start date	end date
1	1	2
2	1

and

id	start date	end date
2		3
3	3	4

How do i combine as to get one table to do my measure on or how do I combine within a measure?

All help is appreciated. :).

[v-angzheng-msft]

Hi, @mikkelakabel 

May I ask if your problem has been solved? Is the above post helpful to you?

If  it does, could you please mark the post which help as Answered? It will help the others in the community find the solution easily if they face the same problem with you. Thank you.😀

[v-angzheng-msft]

Hi, @mikkelakabel 

Try to create a calculated table like this:

Table = 
var _t=SUMMARIZE(UNION('T1','T2'),[id])
var _id=SELECTCOLUMNS(_t,"_id",[id])
var _tt=
ADDCOLUMNS(_id,
    "start",
    IF(MAXX(FILTER(ALL('T1'),'T1'[id]=[_id]),[start date])<>BLANK(),MAXX(FILTER(ALL('T1'),'T1'[id]=[_id]),[start date]),MAXX(FILTER(ALL('T2'),'T2'[id]=[_id]),[start date])),
    "end",
    IF(MAXX(FILTER(ALL('T1'),'T1'[id]=[_id]),[end date])<>BLANK(),MAXX(FILTER(ALL('T1'),'T1'[id]=[_id]),[end date]),MAXX(FILTER(ALL('T2'),'T2'[id]=[_id]),[end date]))
)
return _tt

Result:

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@mikkelakabel , Append table in Power query and the group by and take Min for start and max for end and try 

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

https://docs.microsoft.com/en-us/power-query/group-by

In DAX, Create a common dimension  id

and then take create a common measure check min and max for blank and use the other one

example

if(isblank(Min(Table1[Start Date])), Min(Table2[Start Date]),Min(Table1[Start Date]))

Bridge Table: https://www.youtube.com/watch?v=Bkf35Roman8&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=19