cancel
Showing results for
Did you mean:
Member

## Time Intelligence with 2 calendars

Hi!

This is my scenario:

I have 1 fact table [Fact] with 2 dates and 2 calendars, [CalendarA] and [CalendarB]. I have both calendars with active relationships with the facts table.

One date is [Date A] and the other one is [Date B].

I have two tables in one I use these two formulas:

```Measure A =
CALCULATE(
DISTINCTCOUNT( TableA[Number] );
CalendarA[DateA]
)

Measure A LY =
CALCULATE(
[MeasureA];
SAMEPERIODLASTYEAR( TableA[DateA].[Date] )
)```

And in the other table I use these 2 formulas

```Measure B =
CALCULATE(
DISTINCTCOUNT( TableB[Number] );
CalendarB[DateB]
)

Measure B LY =
CALCULATE(
[MeasureB];
SAMEPERIODLASTYEAR( TableB[DateB].[Date] )
)```

Now, my problem is that I want to filter by CalendarA both tables but the filter only works for the first table visual and not the second one.

Maybe my approach is incorrect.

Any ideas?

Thank you in andvance

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft

## Re: Time Intelligence with 2 calendars

Hi @luxpbi

You may use USERELATIONSHIP Function.Please check attached file.

```Measure B =
CALCULATE(
DISTINCTCOUNT( TableA[Booking number]),USERELATIONSHIP(CalendarA[DateA],TableA[DateB]))```
```Measure B LY =
CALCULATE(
[Measure B],
SAMEPERIODLASTYEAR( CalendarA[DateA].[Date]))```

Regards,

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

## Re: Time Intelligence with 2 calendars

Hi @luxpbi

Regards,

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

## Re: Time Intelligence with 2 calendars

Here is some dummy data:

 DateA DateB Booking number 02/01/2019 02/01/2019 1 03/01/2019 03/01/2019 2 03/01/2019 03/01/2019 1 02/01/2019 02/01/2019 3 02/01/2019 02/01/2019 4 04/01/2019 04/01/2019 5 02/01/2019 02/01/2019 6 02/01/2019 31/12/2018 7 02/01/2019 31/12/2018 8 04/01/2019 10/06/2019 9 04/01/2019 07/01/2019 10 02/01/2019 03/01/2019 11 02/01/2019 03/01/2019 12 03/01/2019 02/02/2019 13 03/01/2019 07/01/2019 14 03/01/2019 02/02/2019 15 03/01/2019 02/02/2019 16 04/01/2019 05/01/2019 17 04/01/2019 05/01/2019 18 05/01/2019 02/02/2019 19 04/01/2018 04/01/2018 20 02/01/2018 02/01/2018 21 03/01/2018 03/01/2018 22 04/01/2018 04/01/2018 23 04/01/2018 04/01/2018 24 02/01/2018 02/01/2018 25 04/01/2018 04/01/2018 26 02/01/2018 02/01/2018 27 05/01/2018 05/01/2018 28 03/01/2018 03/01/2018 29 02/01/2018 02/01/2018 30 04/01/2018 04/01/2018 31 01/01/2018 01/01/2018 32 01/01/2018 01/01/2018 33 01/01/2018 01/01/2018 34 05/01/2018 05/01/2018 35 06/01/2018 31/12/2018 36 01/01/2018 01/01/2018 37 01/01/2018 01/01/2018 38 06/01/2018 10/06/2018 39 04/01/2018 04/01/2018 40 04/01/2018 04/01/2018 41 06/01/2018 06/01/2018 42 03/01/2018 03/01/2018 43 03/01/2018 07/01/2018 44

I want to filter by DateA and see Booking number LY by DateA and Booking number LY by DateB

Microsoft

## Re: Time Intelligence with 2 calendars

Hi @luxpbi

You may link the tableA and tableB with calendar A.Then create the measures.

```Measure A =
CALCULATE(
DISTINCTCOUNT( TableA[Booking number]),
TableA[DateA]
)```
```Measure A LY =
CALCULATE(
[Measure A],
SAMEPERIODLASTYEAR( CalendarA[DateA].[Date]
))```

Regards

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

## Re: Time Intelligence with 2 calendars

First of all I would like to thank to for your speed.

I don't quite understand your answer, do i have to create the Table B, that is equal to Table A?

In my model I don't have 2 Fact tables, I only have 1.

Thank you !

Microsoft

## Re: Time Intelligence with 2 calendars

Hi @luxpbi

You may use USERELATIONSHIP Function.Please check attached file.

```Measure B =
CALCULATE(
DISTINCTCOUNT( TableA[Booking number]),USERELATIONSHIP(CalendarA[DateA],TableA[DateB]))```
```Measure B LY =
CALCULATE(
[Measure B],
SAMEPERIODLASTYEAR( CalendarA[DateA].[Date]))```

Regards,

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

## Re: Time Intelligence with 2 calendars

It works as expected, but I need the next scenario:

If I filter year everything works fine:

but if I filter Month I need to see all future dates for Measure B.

Hope you can help in this scenario.

Thank you a lot !! for your help 🙂

Member

## Re: Time Intelligence with 2 calendars

One Calendar with multiple relationships where one is active and other inactive should work alongside with the DAX function USERELATIONSHIP() -

e.g.

```Revenue by check-out date =
CALCULATE([Amount],
USERELATIONSHIP('date'[date],
'factTable'[check-out date])
)```

where

`Amount = SUM('factTable'[Amount]) `

and your calendar has active relationship between 'factTable'[check-in date] and 'date'[date] and INACTIVE relationship between 'factTable'[check-out date] and 'date'[date]

Microsoft

## Re: Time Intelligence with 2 calendars

Hi @luxpbi

I would suggest you create a new thread on forum so that more community members can see it and provide advice. Please remember to post dummy data and desired result.

Regards,

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

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!