topic Re: DATESBETWEEN function ignoring LASTDATE Measure in DAX Commands and Tips

DATESBETWEEN function ignoring LASTDATE Measure

AlunThomas — Mon, 18 Dec 2023 12:34:31 GMT

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.

Re: DATESBETWEEN function ignoring LASTDATE Measure

some_bih — Mon, 18 Dec 2023 14:00:55 GMT

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

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

Re: DATESBETWEEN function ignoring LASTDATE Measure

AlunThomas — Mon, 18 Dec 2023 15:13:38 GMT

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

Re: DATESBETWEEN function ignoring LASTDATE Measure

some_bih — Mon, 18 Dec 2023 16:17:30 GMT

Hi AlunThomas please share example file with expected output

Re: DATESBETWEEN function ignoring LASTDATE Measure

AlunThomas — Tue, 19 Dec 2023 11:13:09 GMT

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.

Re: DATESBETWEEN function ignoring LASTDATE Measure

AlunThomas — Tue, 02 Jan 2024 08:59:33 GMT

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 🙂

Re: DATESBETWEEN function ignoring LASTDATE Measure

Dangar332 — Tue, 02 Jan 2024 10:01:22 GMT

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