Forum Discussion

Helper V

7 years ago

Solved

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 dat...

v-cherch-msft
7 years ago
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,
Time Intelligence.pbix126 KB

Microsoft Employee

7 years ago

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,

Helper V

7 years ago

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.

v-cherch-msft
Microsoft Employee
7 years ago
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
- luxpbi
  Helper V
  7 years ago
  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 !