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,
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.
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
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 :)