topic Re: How to calcul the sum on fild filtred by two dates slicers in DAX Commands and Tips

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

ouafaa — Thu, 17 Feb 2022 18:30:39 GMT

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

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

amitchandak — Fri, 18 Feb 2022 04:18:31 GMT

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

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

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

ouafaa — Fri, 18 Feb 2022 12:29:30 GMT

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

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

Anonymous — Thu, 24 Feb 2022 08:50:16 GMT

Hi ouafaa ,

Could not reach the file.

Does the problem be solved?

Best Regards,

Jay