Hi all,
So the returning solution when facing multiple dates from different dimensions is turning to a canonical calendar, as suggested by Henric. The idea is pretty straightforward in finding the lowest grain, building a DateLink table followed by a Mastercalendar.
In my situation there are three different dates; an orderdate, a login date and an account activation date. The lowest grain is the UserId, since this is used when placing orders, logging in and gets mandated when creating the account. As such, I'm using Left Join() to create one fact table out of the three different dimensions.
However, when I try Henric's solution I stumble upon a peculiar "recycling" issue. Orders are duplicated over multiple dates. For instance, an order in February also moves to March when, and only when, one customer orders something in February and something in March (2 transactions in total turns to 4), even when mandating in set analysis DateType = {'Field'}.
In the attachment I placed some dummy data which replicates my issue; orders are duplicated when using a canonical / master calendar. Hope someone can offer / send me to a solution.
