Same period display 2 level of measurements using one straight table

[Dee3]

Hello fellows, 

I am trying to solve a persistent question... Hope some experts can settle it...

Table 1 : Sales Data

SalesDate	OrderID	MemberID	OrderAmount
2023-01-01	230101001	1098	100
2023-01-01	230101002	1293	149
2023-01-01	230101003	1344	120
2023-01-02	230102001	1233	12
2023-01-02	230102002	1245	98
2023-01-02	230102003	1098	123

Table 2 : Member Registerion Data

RegisterDate	MemberID
2023-01-01	1098
2022-12-28	1293
2022-12-28	1344
2023-01-01	1233
2023-01-02	1245

And I expected to work out a straight table to display the period and its no. of registered member and no. of purchased member as below:

Period	Registered Member	Purchased Member
2022-12	2	0
2023-01	3	5
2023-02	0	0

(*) since one member purhcased 2 times on 2023-01, so the unique member no. is 5 only..

I tried to do like this.. but seem doesnt work..
column 1 : Date(Floor(Alt(RegisterDate, SalesDate)), 'YYYY-MM') (P.S. i also tried Date(floor(coalesce(RegisterDate, SalesDate)), 'YYYY-MM') but also fail)
column 2 : Count(Distinct {<RegisterPeriod = {"=Date(Floor(RegisterDate), 'YYYY-MM')"}>} MemberID)
column 3 : Count(Distinct {<SalesPeriod = {"=Date(Floor(SalesDate), 'YYYY-MM')"}>} MemberID)

I attached the qvf wth sample data.. any fellow can help..

Thanks. 

Pika

[SterreKapteijns · Accepted solution]

You could also create a master calendar and link it with the dates field to get the year-period field. Then you should rename one MemberID field and count distinct to get the amount of members. 

But I do not know how the whole data model looks like and whether it is desirable to link the sales data with the members data. 

[Dee3]

Thanks. Using Master Calendar method is correct.