Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
luxpbi
Helper V
Helper V

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

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

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.

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.

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.

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 !

 

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.

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 🙂 

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.
Yggdrasill
Responsive Resident
Responsive Resident

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]


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.