Join calendar table with 2 date between from my fact table SSAS

[Anonymous]

Hi

I would like to link a fact table with a start date and an end date with my calendar table. Power BI/SSAS does not allow to link a table using a between. My fact table includes only one element between the start date and the end date. The objective is to be able to display the active information between these 2 dates.

Table Calandar : 

Date	Year Week	Year Month	Year Quarter	Year
18/10/2015	2015-42	2015-10	2015-4	2015
19/10/2015	2015-42	2015-10	2015-4	2015
20/10/2015	2015-42	2015-10	2015-4	2015
21/10/2015	2015-42	2015-10	2015-4	2015
22/10/2015	2015-43	2015-10	2015-4	2015
…..	…..	…..	…..	…..
25/12/2020	2020-52	2020-12	2020-4	2020
26/12/2020	2020-52	2020-12	2020-4	2020
27/12/2020	2020-52	2020-12	2020-4	2020
28/12/2020	2020-52	2020-12	2020-4	2020
29/12/2020	2020-52	2020-12	2020-4	2020
30/12/2020	2020-53	2020-12	2020-4	2020
31/12/2020	2020-53	2020-12	2020-4	2020

Table Fact

Code Client	Code Elément	Start date	End date	Amount
10001	19	12/11/2015	31/12/2016	2 000,00
10001	19	01/01/2017	31/12/2017	2 012,20
10001	19	01/01/2018		2 039,36
10001	22	12/11/2015		63,00
10001	22	01/07/2018	31/05/2020	959,97
10001	22	01/06/2020	31/07/2020	678,70
10001	22	01/08/2020	31/08/2020	815,97
10001	22	01/09/2020		959,97
10001	64	01/07/2018	31/03/2019	4 617,06
10001	64	01/04/2019	31/03/2020	4 660,10
10001	64	01/04/2020	30/09/2020	4 670,35
10001	64	01/10/2020	31/01/2021	0,00
10001	64	01/02/2021		4 670,35
10001	71	01/06/2020	30/09/2020	0,00
10001	GB	12/11/2015	01/09/2021	14 760,00
10001	GB	01/07/2018	10/04/2021	24 123,74

If i choose MonthYear=2020-10, the Amount will be the sum of : 2 039,36 + 63,00 + 959,97 + 0,00 + 4 670,35 + 14 760,00 + 24 123,74

With the sql, the relation between this 2 table will be :

( 'Date'>='Start date' and 'Date' <= 'End date' ) or 'End date' is null

I don't know how to achieve this relationship on the SSAS tabular/PowerBI model.

for more information, in my model the calendar table is linked to other fact tables.

Thanks You for reply.

Best Regards

[Anonymous]

@v-deddai1-msft 

Hello,

I was not able to implement your solution because of the problem I raised in my previous message about using my model on other dimensions.
Do you have a solution to avoid Cartesian products?

Thank you for your feedback.

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ，

Don‘t create relationship between your date table and fact table，and you can create the following measure for visual level filter:

Measure = VAR a = MAX(Dim_Date[Date]) var b = MIN(Dim_Date[Date]) return IF(MAX('Table'[Start date])<=b&&(MAX('Table'[End date])>=a ||ISBLANK(MAX('Table'[End date]))),1,0)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Anonymous]

Hello,
Sorry for my late answer.

Thank you very much for your reply.  I tried your solution by removing the relationship between the fact table and the time table.

However, I get Cartesian products when I use other dimensions that are related to my fact table.
It brings back all available periods between the fact table and the time table.
How can I delete the Cartesian products in order to indicate him to use the joints of the other dimensions related to the fact table?

When I use "billing element code" from the dim_eltfac table with a period and the measurement, I get a cartesian product.

Thanks

[amitchandak]

@Anonymous , I can date table do not have join with fact table

measure =
var _min =minx(allselected(date),date[date])
var _max =maxx(allselected(date),date[date])
return
calculate(Sum(Table[Amount]), filter(Table, Table[Start Date] <=_max && (isblank(Table[End Date]) || Table[End Date] >=_min)))

If have join with start date

measure =
var _min =minx(allselected(date),date[date])
var _max =maxx(allselected(date),date[date])
return
calculate(Sum(Table[Amount]), filter(Table, Table[Start Date] <=_max && (isblank(Table[End Date]) || Table[End Date] >=_min)))

measure =
var _min =minx(allselected(date),date[date])
var _max =maxx(allselected(date),date[date])
return
calculate(Sum(Table[Amount]), filter(Table, Table[Start Date] <=_max && (isblank(Table[End Date]) || Table[End Date] >=_min)), crossfilter(Table[Start Date],date[date], none))

[Anonymous]

Hi

Thanks you for you reply. I try add this 3 messure but have not right result ;(

For now i have made the relation with "Start Date" between the fact table et date table

When I filter YearMonth 2020-10 (in my date table), i have only the element where Start Date is equals 2020-10. But I would like all the element actif 

I have just 

Code Client	Code Element	Start date	End date	Amount
10001	64	01/10/2020	31/01/2021	0

But I would like this résult : 

Code Client	Code Elément	Start date	End date	Amount
10001	19	01/01/2018		2 039,36
10001	22	12/11/2015		63
10001	22	01/09/2020		959,97
10001	64	01/10/2020	31/01/2021	0
10001	64	01/02/2021		4 670,35
10001	GB	12/11/2015	01/09/2021	14 760,00
10001	GB	01/07/2018	10/04/2021	24 123,74

because the analysis date '2010-10' is between start date and end date

How to model and realize the joint to get this result?

Thanks You for the reply

Best Regards,

Amine