Using DATESBETWEEN and FILTER

[donalmcnamee2]

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 · Accepted solution]

Hi,

did you create the column or measure ?

I did the column and it worked.

Appreciate your Kudos

[MahyarTF]

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]

@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!

[Ashish_Mathur · Accepted solution]

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.

[donalmcnamee2]

@Ashish_Mathur 

This solution worked great also.

Thanks for your help!

[Ashish_Mathur]

You are welcome.

[MahyarTF · Accepted solution]

Hi,

did you create the column or measure ?

I did the column and it worked.

Appreciate your Kudos

[donalmcnamee2]

@MahyarTF 

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