Forum Discussion
DATESBETWEEN function ignoring LASTDATE Measure
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.
6 Replies
Hi AlunThomas using LASTDATE / FIRSTDATE perform context transition...
Question: [Family Weight [kg]]] is column or measure?
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
Hi AlunThomas please share example file with expected output
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.
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
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 🙂
