cancel
Showing results for
Did you mean:
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)

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

## Re: Comparing multiple dates in date table

Hi @Greenwoodr,

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.
Regular Visitor

## Re: Comparing multiple dates in date table

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!!

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.

Announcements

#### Challenge: Can You Solve These?

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

#### Community News & Announcements

Get your latest community news and announcements.

#### 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.

#### Win Power BI Swag with Community Kudopalooza!

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

Top Kudoed Authors
Users Online
Currently online: 42 members 954 guests
Recent signins: