Forum Discussion

donalmcnamee2's avatar
donalmcnamee2
New Member
4 years ago
Solved

Using DATESBETWEEN and FILTER

Hi,    I have two unrelated tables:  Fuel Purchases Days Worked The Fuel Purchases table records the date a vehicle refuled as well as the date it was previously refuled. The table Days Worke...
Need Help
  • MahyarTF's avatar
    MahyarTF
    4 years ago

    Hi,

    did you create the column or measure ?

    I did the column and it worked.

     

    Appreciate your Kudos

  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    Hi,

    This calculated column formula works

    Column = CALCULATE(COUNT('Days Worked'[Date]),FILTER('Days Worked','Days Worked'[Vehicle Registration]=EARLIER('Fuel Purchases'[Vehicle Registration])&&'Days Worked'[Date]>=EARLIER('Fuel Purchases'[Last Fuel Purchase Date])&&'Days Worked'[Date]<=EARLIER('Fuel Purchases'[Fuel Purchase Date])&&'Days Worked'[Has the vehicle worked today?]=TRUE()))

    Hope this helps.

MahyarTF's avatar
MahyarTF
Memorable Member
4 years ago

Hi,

I did create the measure in Fuel Purchase table as below :

No. of Days =
Var _StartDate = SELECTEDVALUE(Sheet152[Last Fuel Purchase Date])
Var _EndDate = SELECTEDVALUE(Sheet152[Fuel Purchasing Date])
Var _DayNo = CALCULATE(COUNT(Sheet153[Vehicle Registration]),
                       filter(Sheet153, Sheet153[Has the vihicle Worked Today ?] = TRUE()),
                       DATESBETWEEN(Sheet153[Date], _StartDate, _EndDate)
                       )
Return _DayNo

 

Appreciate your Kudos

  • donalmcnamee2's avatar
    donalmcnamee2
    New Member
    4 years ago

    MahyarTF 

    That's exactly what I'm looking for!

     

    However, when I try to recreat it, I'm getting a different result to the one you're getting. 

     

     

    It must be something simple as the data set you're using is exactyl the same as mine. 

     

    My PIBX can be found here if you would like to take a look: https://dl.archwayproducts.com/downloads/BI/query.pbix 

     

    Many thanks!

    • MahyarTF's avatar
      MahyarTF
      Memorable Member
      4 years ago

      Hi,

      did you create the column or measure ?

      I did the column and it worked.

       

      Appreciate your Kudos

      • donalmcnamee2's avatar
        donalmcnamee2
        New Member
        4 years ago

        MahyarTF 

        Yes, using a Calclulated Column instead of a Measure worked great. 

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      4 years ago

      Hi,

      This calculated column formula works

      Column = CALCULATE(COUNT('Days Worked'[Date]),FILTER('Days Worked','Days Worked'[Vehicle Registration]=EARLIER('Fuel Purchases'[Vehicle Registration])&&'Days Worked'[Date]>=EARLIER('Fuel Purchases'[Last Fuel Purchase Date])&&'Days Worked'[Date]<=EARLIER('Fuel Purchases'[Fuel Purchase Date])&&'Days Worked'[Has the vehicle worked today?]=TRUE()))

      Hope this helps.