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 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,
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,