Forum Discussion

donalmcnamee2's avatar
donalmcnamee2
New Member
3 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
    3 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
    3 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
    Memorable 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

      • MahyarTF's avatar
        MahyarTF
        Memorable Member

        Hi,

        did you create the column or measure ?

        I did the column and it worked.

         

        Appreciate your Kudos