Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ouafaa
Helper II
Helper II

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

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 :

ouafaa_0-1645122595075.png

 

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

3 REPLIES 3
v-jayw-msft
Community Support
Community Support

Hi @ouafaa ,

 

Could not reach the file.

Does the problem be solved?

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
amitchandak
Super User
Super User

@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 ) )

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors