How to calcul the sum on fild filtred by two dates slicers

[ouafaa]

I have the tables :
Reference_products wich contains fields : Ref_product libelle, produit
product_history wich contains columns : ref_product_hist, ref_product, start_date_history and end_date_history and number_product_available
dimDateStart
dimDateEnd

the table product_history table is linked to 2 calendar tables:
dimDateStart through the start_history_date field
dimDateEnd through the end_history_date field

I need to have in my report 2 date slicers : the first one to choise a start date and another to choise an end date
and I need to have a mesure wich calculate the sum of the number_product_available field in the table start_history_date
where :
start_history_date > selected date from the first slicer date (dimDateStart)
and end_history_date < selected date from the end date slicer (dimDateEnd)

there is my model :

NB: I just start learning dax and I have no idea how I could do it, I thank you for any help
you will find my source file and my pbix in this link : https://we.tl/t-phCWQvx889
I thank you again

[Anonymous]

Hi @ouafaa ,

Could not reach the file.

Does the problem be solved?

Best Regards,

Jay

[amitchandak]

@ouafaa , I think it can work with an independent date table / Or two independent date table(Do not join) , If you join Before or after slicer or use crossfilter to remove join

// Date1, Date2 are independent tables

new measure =
var _max = maxx(allselected(Date2),Date2[Date])
var _min = mainx(allselected(Date1),Date1[Date])
return
calculate( sum(Table[Value]), filter('Table', 'Table'[start_date_history ] >=_min && 'Table'[end_date_history ] <=_max))

// Date are joined 

new measure =
var _max = maxx(allselected(Date2),Date2[Date])
var _min = mainx(allselected(Date1),Date1[Date])
return
calculate( sum(Table[Value]), filter('Table', 'Table'[start_date_history ] >=_min && 'Table'[end_date_history ] <=_max) ,CROSSFILTER ( Table[start_date_history ], Date1[Date], NONE )

CROSSFILTER ( Table[end_date_history ], Date2[Date], NONE ) )

[ouafaa]

Hello @amitchandak,

Thank you very much, I will test those solutions and will update you asap.

the solution that I used is that i declared juste one dimDate and I did two inactive relationships consucutively with start_date_history  and  end_date_history and then I did a calculate sum with two filter

the firest one when MIN(dimDATE) <= start_date_history  and the second filter with MAX(dimDATE)>=end_date_history 

but I don't know if it is optimised if we consider the performance ?

Thank you again for your help