Forum Discussion
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).
Hi,
did you create the column or measure ?
I did the column and it worked.
Appreciate your Kudos
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
- MahyarTFMemorable 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 _DayNoAppreciate your Kudos
- donalmcnamee2New Member
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!
- MahyarTFMemorable Member
Hi,
did you create the column or measure ?
I did the column and it worked.
Appreciate your Kudos