Forum Discussion
Helper VTime 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
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,
8 Replies
v-cherch-msftMicrosoft 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,
luxpbiHi v-cherch-msft,
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
Thank you for your help.
- v-cherch-msft
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