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 Worked simply records the date a vehicle was on the road and wheather it worked that day or not. 

 

 

 

I'm looking to define a Measure on the Fuel Purchases table that will tell me the number of days that a particular vehicle worked between refueling dates (i.e. the number of days where 'Has the vehicle worked today?' = TRUE in the Days Worked table.)

 

 

 

Days Between Refuling= 
CALCULATE(
    COUNT('days_worked_table'[vehicle registration]), 
        DATESBETWEEN(
            'days_worked_table'[date],
            MIN('fuel_purchases'[previous_fuel_purchase_date]), 
            MIN('fuel_purchases'[fuel_purchase_date])
        )
)

 

 

 

But I'm clearly missing something to filter this by Vehicle Registration. 

 

Basically what' I'm trying to achieve is a Measure as per the column 'No. of days worked between refuling' in the example Fuel Purchases table above (highlighted in Yellow). 

  • 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.

7 Replies

  • MahyarTF's avatar
    MahyarTF
    Icon for Memorable Member rankMemorable Member

    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

      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
        Icon for Memorable Member rankMemorable Member

        Hi,

        did you create the column or measure ?

        I did the column and it worked.

         

        Appreciate your Kudos