Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi,
I have two unrelated tables:
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).
Solved! Go to Solution.
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.
Hi,
I did create the measure in Fuel Purchase table as below :
Appreciate your Kudos
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!
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.
You are welcome.
Hi,
did you create the column or measure ?
I did the column and it worked.
Appreciate your Kudos
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
108 | |
99 | |
38 | |
36 |
User | Count |
---|---|
149 | |
122 | |
76 | |
74 | |
52 |