Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
donalmcnamee2
New Member

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. 

 

donalmcnamee2_0-1662102284255.png

 

donalmcnamee2_1-1662102298309.png

 

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

2 ACCEPTED SOLUTIONS

Hi,

did you create the column or measure ?

I did the column and it worked.

MahyarTF_0-1662164607372.png

 

Appreciate your Kudos

Mahyartf

View solution in original post

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

7 REPLIES 7
MahyarTF
Memorable Member
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
MahyarTF_0-1662105122632.png

 

Appreciate your Kudos

Mahyartf

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

 

donalmcnamee2_0-1662120363338.png

 

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur 

This solution worked great also.

Thanks for your help!

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,

did you create the column or measure ?

I did the column and it worked.

MahyarTF_0-1662164607372.png

 

Appreciate your Kudos

Mahyartf

@MahyarTF 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.