Comparing multiple dates in date table

[Greenwoodr]

Hi,

I have a question about the use of Keys on date tables and what is best practice:

I have the follwoing scenario where I have a central fact table (subscriptions in the picture) which contains separate keys for each possible date associated with each sale in my sales / subscription table (1) for sign up,2) Date Paid,3)End date of sale)

This works well undermost circumstances but I cannot get my head around how to compair across these dates in the same query

Active S = 
calculate  (
      sum(PaidSupporter),
      filter  (
            all('subscriptions'),
            'subscriptions'[Paiddate] <=max('Date'[Current_month_end_Date])
             && ('subscriptions'[ENDDATE] > max('Date'[Current_month_end_Date])
             || 'subscriptions'[ENDDATE]=blank())
              )
           )

  This only works because I brought the actual dates into the subscriptions table rather than using the active relationships (or changing the relationship with the USERELATIONSHIP FUNCTION with would need to be done twice in this example)

Is bringing the actual date into the  table the best way to do this or can I use the relationships / keys to achieve the same thing.

If this is the best way do I do away with the keys altogether and just create relationships on the actual dates?

Cheers 

[v-jiascu-msft]

Hi @Greenwoodr,

Can you share a dummy sample, please? Please mask the sensitive parts first.

I can't see the actual dates that were brought to another table in the formula. 

Where is the context from in the visual?

Best Regards,

[Greenwoodr]

Here is a sample of the data in the subscription table - each Dateid links to the date dimension dateid with the signup date being the active relationship

I think my question is more of an idealistic one. I was under the impression that the best designs kept the date values in the date dimensions, linked by the a key rather that actual dates, and ideally one but splitting out into additional date tables (i.e have 1 date table fot the signup date relationship, 1 for paiddate and 1 for end date) is an acceptable choice.

However to use the equation I have I could only get it to work if I put the actual date in the main table which kind of defeats the object.

How can I change the relationships so I can access the date values in the date table more than once in the equation

so this code

 'subscriptions'[Paiddate] <=max('Date'[Current_month_end_Date])
             && ('subscriptions'[ENDDATE] > max('Date'[Current_month_end_date]

becomes (something like:

'date'[date]<=max('Date'[Current_month_end_Date])           -- Using Paiddate relationship
 && ('date'[date]> max('Date'[Current_month_end_Date])      -- using enddate relationship

or could you use a lookup of somekind to use the PaidDateID to return the date value into this measure 

Lookup('subscription'[Paiddateid],'Date'[Dateid],[date] <=max('Date'[Current_month_end_Date])

Is this possible - As my equation works I am really looking for the most effiecient way of using the date table and avoid replication of information in tables {having the ID and the actual date in the main tables is a bit pointless)

Cheers - hope this makes sense!!

[v-jiascu-msft]

Hi @Greenwoodr ,

Why would you like to use more than one date? In other words, what's the result of your sample data? 

Best Regards,