Forum Discussion

AlunThomas's avatar
AlunThomas
Frequent Visitor
2 years ago

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

  • some_bih's avatar
    some_bih
    Icon for Community Champion rankCommunity Champion
    2 years ago

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

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

  • AlunThomas's avatar
    AlunThomas
    Frequent Visitor
    2 years ago

    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

     

  • AlunThomas's avatar
    AlunThomas
    Frequent Visitor
    2 years ago
    MonthProductTotalWeight[kg]

    01/06/2023

    Product A

    		120
    05/02/2022Product A100
    06/02/2023Product A50
    01/01/2023Product A60
    03/04/2022Product A100
    03/04/2022Product B120

     

    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:

    ProductTotalWeight[kg]
    Product A210
    Product B120

     

    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's avatar
      Dangar332
      Icon for Resident Rockstar rankResident Rockstar
      2 years ago

      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's avatar
    AlunThomas
    Frequent Visitor
    2 years ago

    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 🙂