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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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