Forum Discussion
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,
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,
Hi v-cherch-msft ,
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 :)
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]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,
