DATESBETWEEN function ignoring LASTDATE Measure

[AlunThomas]

Hi Everyone;

Firstly - thank you to the community for your help. My last question was answered switfly and professionally. I am new to Power BI so appologies in advance if i ask a stupid question.

- I have a table of data described by a date column finished_goods[Month] in the format dd/mm/yyyy. 

- I have created two measures to establish the max/min date of the data: 

            - MaxDateFG = LASTDATE(finished_goods[Month])

            - MinDateFG = FIRSTDATE(finished_goods[Month])

- I have a slider that selects a date window, and i can see my MaxDataFG and MinDataFG dates changing as the slider changes, so that appears to be working.

- I am then trying to calculate some product weights within the date range using this: 

            - [Family Weight [kg]]] = CALCULATE(sumx(finished_goods,finished_goods[TotalWeight[kg]]]), DATESBETWEEN(finished_goods[Month],[MinDateFG],[MaxDateFG]))

But this returns all weights and appears to ignore the date range. If i replace the code with hardcoded dates (as below), the filter works as expected:

            - [Family Weight [kg]]] = CALCULATE(sumx(finished_goods,finished_goods[TotalWeight[kg]]]), DATESBETWEEN(finished_goods[Month],"01/01/2020", "01/01/2022"))

What am i doing wrong here please? thanks for your help in advance.

[AlunThomas]

Hi All: for anyone who is also struggling with this question it looks like (from my own limited knowledge and forum searching) that you can't dynamically change/filter a data set using a value from a measure (which seems like an oversight?!).

Perhaps one of the "superusers" might know if a way...but only the super clever ones 🙂

[AlunThomas]

Month	Product	TotalWeight[kg]
01/06/2023	Product A	120
05/02/2022	Product A	100
06/02/2023	Product A	50
01/01/2023	Product A	60
03/04/2022	Product A	100
03/04/2022	Product B	120

I then want a slider to select a date range from, say, 03/04/2022 to 06/02/2023 and this would then return weights by product for the given date range only. So the resulting "answer" would look like this:

Product	TotalWeight[kg]
Product A	210
Product B	120

My intention was to use the measures MaxDateFG (ie 06/02/2023) and MinDateFG (ie 03/04/2022) to establish the filter dates from the slider.  But my measures seem to be ignoring the slider filters. Hope i am making sense. 

[Dangar332]

hi, @AlunThomas 

your code is right but when you select date range from slicer then slicer include all date bw your first and last date .

means 

from your above data if you select date range bw (03/04/2022 to 06/02/2023)  slicer include all date from 
(03/04/2022 to 06/02/2023 ) which is 91 dates not 4 date .

so when you move range  your range not exced actual date(present in table ) so data not change and you feel that above code is not working

i hope you clear about that

[AlunThomas]

Hi some_bih, thanks for coming back to me...

Hope i have understood your question correctly:

- finished_goods[TotalWeight[kg] is column of data in the finished_goods table. So there will be multiple rows of data potentially sharing the same date - but i am only looking for those rows within the specified date range.

- MaxDateFG is a measure

- MinDateFG is a measure

[some_bih]

Hi @AlunThomas please share example file with expected output 

[some_bih]

Hi @AlunThomas using LASTDATE / FIRSTDATE perform context transition...

Question: [Family Weight [kg]]] is column or measure?