The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi guys.
So, I have a table with information of cars location of any given time, where car movements are gives me a new row.
CarId | Location | StartDate | EndDate | No. of days |
1 | A | 1/1/2020 | 1/20/2020 | 20 |
1 | B | 1/20/2020 | 1/25/2020 | 5 |
1 | A | 1/25/2020 | 12/31/2020 | 340 |
2 | C | 10/15/2020 | 11/30/2020 | 46 |
2 | A | 11/30/2020 | 12/31/2020 | 32 |
What I want to calculate is the number of "Car days" available in various periods (per day, per month, per quarter etc). For example, I want the measure to say that at 1/1/2020 there were 1 Car day (since only Car 1 was available on Location A - Car 2 didnt "exist" at that point in time). Further, in January 2020 on Location A, there were 20 (row A) + 6 (row 3 - this location data is calculated by StartDate being 1/25/2020 but end of month is 1/31/2020) = 26 available car days.
So, the issue I have is creating the logic where based on Min and Max dates i sum the number of days for each row, given the period I look at. If I evaluate per month basis, the Min(Date) is the 1st of the month and the Max(date) is the last day of the month.
I do of course have a standard Date table.
Thanks,
Ruben
Solved! Go to Solution.
Hi @rubentj
I didn't test to be honest, but please try
Mumber of Days =
VAR MinDate =
MIN ( 'Date'[Date] )
VAR MaxDate =
MAX ( 'Date'[Date] )
VAR Dates1 =
CALENDAR ( MinDate, MaxDate )
VAR Cars =
CALCULATETABLE (
'Cars Data',
CROSSFILTER ( 'Cars Data'[StartDate], 'Date'[Date], NONE )
) -- in case no relationship just use 'Cars Data'
RETURN
SUMX (
Cars,
VAR Dates2 =
CALENDAR ( 'Cars Data'[StartDate], 'Cars Data'[EndtDate] )
RETURN
COUNTROWS ( INTERSECT ( Dates1, Dates2 ) )
)
Hi @rubentj ,
Does the above reply help?
If the problem is resolved, please mark the reply as solution.
Best Regards,
Jay
Hi @rubentj
I didn't test to be honest, but please try
Mumber of Days =
VAR MinDate =
MIN ( 'Date'[Date] )
VAR MaxDate =
MAX ( 'Date'[Date] )
VAR Dates1 =
CALENDAR ( MinDate, MaxDate )
VAR Cars =
CALCULATETABLE (
'Cars Data',
CROSSFILTER ( 'Cars Data'[StartDate], 'Date'[Date], NONE )
) -- in case no relationship just use 'Cars Data'
RETURN
SUMX (
Cars,
VAR Dates2 =
CALENDAR ( 'Cars Data'[StartDate], 'Cars Data'[EndtDate] )
RETURN
COUNTROWS ( INTERSECT ( Dates1, Dates2 ) )
)
Hi again @tamerj1.
So the solution worked as per my requirement.
However, I have come up with another thing I need to adjust for.
Now I count the car two times on the dates which have start and end dates. More specifically, if a Car has end date 31 Dec 2020, another entry has the start date 31 Dec 2020, meaning that I am double counting the cars.
I tried doing an easy fix to this by setting the end date to the date before, but this gives me the problem of the end date being previous to the start date, and this gives me an error.
Do you have any suggestions?
Hi @rubentj
have you tried to minus one from EndDate?
Mumber of Days =
VAR MinDate =
MIN ( 'Date'[Date] )
VAR MaxDate =
MAX ( 'Date'[Date] )
VAR Dates1 =
CALENDAR ( MinDate, MaxDate )
VAR Cars =
CALCULATETABLE (
'Cars Data',
CROSSFILTER ( 'Cars Data'[StartDate], 'Date'[Date], NONE )
) -- in case no relationship just use 'Cars Data'
RETURN
SUMX (
Cars,
VAR Dates2 =
CALENDAR ( 'Cars Data'[StartDate], 'Cars Data'[EndtDate] - 1 )
RETURN
COUNTROWS ( INTERSECT ( Dates1, Dates2 ) )
)
Yes, but the problem is that in some cases, the start and end date is at the same date, but different time stamps. That gives me an error in the calendar function. Is it possible to exclude those cases from the calculation?
In addition, I have a timestamp in a column - is it possible to say that "if the start time stamp is after 12:00AM, use the following date" && "if end date is before 12:00AM, use the previous date"
Thank you so much for your help @tamerj1
Please try
VAR Mumber of Days =
VAR MinDate =
MIN ( 'Date'[Date] )
VAR MaxDate =
MAX ( 'Date'[Date] )
VAR Dates1 =
CALENDAR ( MinDate, MaxDate )
VAR Cars =
CALCULATETABLE (
'Cars Data',
CROSSFILTER ( 'Cars Data'[StartDate], 'Date'[Date], NONE )
) -- in case no relationship just use 'Cars Data'
RETURN
SUMX (
Cars,
VAR Dates2 =
CALENDAR (
'Cars Data'[StartDate],
MAX ( 'Cars Data'[StartDate], 'Cars Data'[EndtDate] - 1 )
)
RETURN
COUNTROWS ( INTERSECT ( Dates1, Dates2 ) )
)
Thank you so much, this is art!!! - it looks very good, and at a first glance, it seems to work. Will do some reconsiliation.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
User | Count |
---|---|
18 | |
18 | |
17 | |
15 | |
13 |
User | Count |
---|---|
36 | |
34 | |
19 | |
18 | |
18 |