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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
rubentj
Frequent Visitor

Create measure based on start and end date, and parameter value

Hello guys - so i've been stuck on a problem, and I'm not sure it's solvable in the way I want to. 

 

I want to calculate utilization on rental cars, and the problem is that the number of available cars on various locations is changing at all times. So basically, I have a problem with three dimensions. 

 

Let me describe the challenge: 

 

Let's say a car is located at location X - this is an estimation of where the car is located in the future.

 

Example: Car 1 is planned located at location X from (1'Jan'2021) until 31'Dec'2021. 

 

Then, there is a change in the plans. The plans are changing 1'Feb'2021 (Index 3 in sample data) - and the changes is that the car is planned located at location Y from 1'Mar'2021 until 31'May'2021, and relocated back at location X from 1'Jun'2021 until 31'Dec'2021 (Index 4 in sample data). This means that the original plan also changes (index 2 in sample data).

 

Here are some example data: 

 

IndexPlan#CarIDCarClassLocationStartDateLocationEndDateLocationCreatedLastChanged
11100BKX01-01-202112-31-202101-01-202102-01-2021
21100BKX01-01-202101-31-202102-01-2021null
32100BKY02-01-202105-31-202102-01-2021null
42100BKY06-01-202112-31-202102-01-2021null

 

So the tricky part here: If i want to know how many cars there was at location X, based on the knowledge we had mid January, i.e. 15'Jan'2021, I want the result to be that there is one car for the whole year (that was the knowledge I had at that time). 15'Jan'2021 is a parameter input (slicer). 

 

Further, if I want to know how the situation was at 15'Feb, I want the result to be one car at Location X from 1'Jan-31'Jan, one car at location Y between 1'Feb'2021-31'May'2021, and one car between 1'Jun'2021-31'Dec'2021. 

 

This is input in utilization calculation. I want to put this into a line chart, where I can change the parameter input to vary. Anyone got a suggestion? I have been searching around for a week without finding any suitable way. I can generate a new table listing all dates the car is at certain locations, per every unique plan, but the dataset will be very large and not manageable. 

 

 

1 ACCEPTED SOLUTION
PaulOlding
Solution Sage
Solution Sage

Hi @rubentj 

 

This article discussing data with start and end dates. https://www.daxpatterns.com/events-in-progress/ 

You have an added complication where you want an as at date selection too.  This measure uses 2 disconnected date tables, one to use as a slicer & the other to use in a chart.

Car Count = 
VAR _AsAtDate = MAX('As At Date'[Date])
VAR _CurrentLocationDate = MAX('Location Date'[Date])
VAR _Result = 
CALCULATE(
	COUNTROWS('Table'),
	'Table'[Created] <= _AsAtDate,
	(ISBLANK('Table'[LastChanged]) || 'Table'[LastChanged] > _AsAtDate),
	'Table'[StartDateLocation] <= _CurrentLocationDate,
    'Table'[EndDateLocation] > _CurrentLocationDate
)
RETURN
    _Result

 

PaulOlding_0-1634572924404.png

 

PaulOlding_1-1634572956464.png

 

View solution in original post

2 REPLIES 2
PaulOlding
Solution Sage
Solution Sage

Hi @rubentj 

 

This article discussing data with start and end dates. https://www.daxpatterns.com/events-in-progress/ 

You have an added complication where you want an as at date selection too.  This measure uses 2 disconnected date tables, one to use as a slicer & the other to use in a chart.

Car Count = 
VAR _AsAtDate = MAX('As At Date'[Date])
VAR _CurrentLocationDate = MAX('Location Date'[Date])
VAR _Result = 
CALCULATE(
	COUNTROWS('Table'),
	'Table'[Created] <= _AsAtDate,
	(ISBLANK('Table'[LastChanged]) || 'Table'[LastChanged] > _AsAtDate),
	'Table'[StartDateLocation] <= _CurrentLocationDate,
    'Table'[EndDateLocation] > _CurrentLocationDate
)
RETURN
    _Result

 

PaulOlding_0-1634572924404.png

 

PaulOlding_1-1634572956464.png

 

Wow! Thank you so much @PaulOlding ! This is magic!!!

 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors