cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
luxpbi Member
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 v-cherch-msft
Microsoft

Re: Time Intelligence with 2 calendars

Hi @luxpbi 

You may use USERELATIONSHIP Function.Please check attached file.

Reference:https://www.sqlbi.com/articles/userelationship-in-calculated-columns/

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.

View solution in original post

8 REPLIES 8
Microsoft v-cherch-msft
Microsoft

Re: Time Intelligence with 2 calendars

Hi @luxpbi 

It is very hard to provide an accurate solution without looking at sample data.Please explain more about your expected output.Could you upload the .pbix file to OneDrive and post the link here? Do mask sensitive data before uploading.

How to Get Your Question Answered Quickly

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.
luxpbi Member
Member

Re: Time Intelligence with 2 calendars

Hi @v-cherch-msft,

 

Here is some dummy data:

DateADateBBooking number
02/01/201902/01/20191
03/01/201903/01/20192
03/01/201903/01/20191
02/01/201902/01/20193
02/01/201902/01/20194
04/01/201904/01/20195
02/01/201902/01/20196
02/01/201931/12/20187
02/01/201931/12/20188
04/01/201910/06/20199
04/01/201907/01/201910
02/01/201903/01/201911
02/01/201903/01/201912
03/01/201902/02/201913
03/01/201907/01/201914
03/01/201902/02/201915
03/01/201902/02/201916
04/01/201905/01/201917
04/01/201905/01/201918
05/01/201902/02/201919
04/01/201804/01/201820
02/01/201802/01/201821
03/01/201803/01/201822
04/01/201804/01/201823
04/01/201804/01/201824
02/01/201802/01/201825
04/01/201804/01/201826
02/01/201802/01/201827
05/01/201805/01/201828
03/01/201803/01/201829
02/01/201802/01/201830
04/01/201804/01/201831
01/01/201801/01/201832
01/01/201801/01/201833
01/01/201801/01/201834
05/01/201805/01/201835
06/01/201831/12/201836
01/01/201801/01/201837
01/01/201801/01/201838
06/01/201810/06/201839
04/01/201804/01/201840
04/01/201804/01/201841
06/01/201806/01/201842
03/01/201803/01/201843
03/01/201807/01/201844

 

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

 

Thank you for your help.

Microsoft v-cherch-msft
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.
luxpbi Member
Member

Re: Time Intelligence with 2 calendars

Hi @v-cherch-msft,

 

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 v-cherch-msft
Microsoft

Re: Time Intelligence with 2 calendars

Hi @luxpbi 

You may use USERELATIONSHIP Function.Please check attached file.

Reference:https://www.sqlbi.com/articles/userelationship-in-calculated-columns/

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.

View solution in original post

Highlighted
luxpbi Member
Member

Re: Time Intelligence with 2 calendars

Hi @v-cherch-msft ,

 

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

If I filter year everything works fine: Captura.PNG

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

Captura2.PNG

Hope you can help in this scenario. 

 

Thank you a lot !! for your help 🙂 

Yggdrasill Member
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 v-cherch-msft
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.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

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?

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

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!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors