cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Greenwoodr Regular Visitor
Regular Visitor

Comparing multiple dates in date table

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)

 

BI Picture.JPG

 

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 

3 REPLIES 3
v-jiascu-msft Super Contributor
Super Contributor

Re: Comparing multiple dates in date table

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,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Greenwoodr Regular Visitor
Regular Visitor

Re: Comparing multiple dates in date table

BI Picture2.JPG

 

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 Super Contributor
Super Contributor

Re: Comparing multiple dates in date table

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,

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 42 members 954 guests
Please welcome our newest community members: