Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I am trying to calculate the spaces occupied in the car park area by day and to show whether they are arriving on date, or were already in car park and have yet to depart.
For example: on the 04/01/2019, there would be 3 customers in the car park (of which 1 arrived on the day, and 3 were already in the car park)
Customer, Date Arrival, Date Departure
123, 01/01/2019, 03/01/2019
124, 02/01/2019, 06/01/2019
125, 01/01/2019, 07/09/2019
126, 04/01/2019, 05/01/2019
In sql I would use the following code, is there a way to replicate this in powerbi?
select a.Date,count(a.[customer]),a.Flag, CarPark
from
(SELECT DISTINCT D.Date, F.[customer],case when F.[Booking_Arr_Date]=D.Date then 1 else 0 end as "Flag",
FROM [DimDates] D
JOIN [Car Park Data] F
ON F.[Booking_Arr_Date]<=D.Date AND (F.[Booking_Dep_Date]>=D.Date)
) a
group by a.Date,a.Flag,CarPark
Hey @ninakarsa ,
first I created a very simple calendar table using this DAX (there are much more sophisticated DAX statements available, just search for DAX calendar):
Calendar =
var DateStart = MIN('Table'[Date Arrival])
var DateEnd = MAX('Table'[Date Departure])
return
CALENDAR(DateStart , DateEnd)
Please make sure that there is no relationship between the Calendar table and the containing the arrival and departure date, this table is called table in my example.
I created the measure "present" using this DAX:
present =
var _date = CALCULATE(MAX('Calendar'[Date]))
return
CALCULATE(
COUNTROWS('Table')
, FILTER(
'Table'
, 'Table'[Date Arrival] <= _date && 'Table'[Date Departure] >= _date
)
)
and the measure arrived using this DAX:
arrived =
var _date = CALCULATE(MAX('Calendar'[Date]))
return
CALCULATE(
COUNTROWS('Table')
, FILTER(
'Table'
, 'Table'[Date Arrival] = _date
)
)
This allows to create a table visual like so:
Please be aware that the date column in the table visual above is the date column from the Calendar table.
If you are searching for event-in-progress you will find some articles that provide more background for similar questions than my answer will be able to provide.
Hopefully this is what you are looking for
Regards,
Tom
@TomMartens thanks for this.
Now lets say I have a table with information regarding to the car park table A which gives the number of spaces in the car park area during a given timeframe.
Table B, is a calcuated table showing by date customers in the car park area, so for example on the 01/05/2019 1 customer was in car park zone A & left car park area, 3 customers were in car park zone B with 3 in left car park area.
I want to combine the two tables to say that when Date from table B is between 'from date' and 'to date' in table A and car park and car park area from B is equal to car park and car park area from table A then return the number of spaces in table B from table A. This should return table C
And finally to create a measure which will return results in table d
Table A:
Car Park | Car Park Area | From Date | To Date | Spaces |
Zone A | Left | 01/05/2019 | 20/05/2019 | 10 |
Zone A | Right | 01/05/2019 | 20/05/2019 | 20 |
Zone B | Left | 01/05/2019 | 20/05/2019 | 30 |
Zone B | Right | 01/05/2019 | 20/05/2019 | 15 |
Zone A | Left | 01/06/2019 | 20/06/2019 | 15 |
Zone A | Right | 01/06/2019 | 20/06/2019 | 60 |
Zone B | Left | 01/06/2019 | 20/06/2019 | 35 |
Zone B | Right | 01/06/2019 | 20/06/2019 | 70 |
Table b:
Date | CustomerID | CarPark | CarPark Area |
01/05/2019 | 123 | Left | ZoneA |
01/05/2019 | 127 | Left | ZoneB |
01/05/2019 | 130 | Left | ZoneB |
01/05/2019 | 133 | Left | ZoneB |
Table c
Date | CustomerID | CarPark | CarPark Area | Spaces |
01/05/2019 | 123 | Left | ZoneA | 10 |
01/05/2019 | 127 | Left | ZoneB | 30 |
01/05/2019 | 130 | Left | ZoneB | 30 |
01/05/2019 | 133 | Left | ZoneB | 30 |
then measures to show
01/05/2019 occupancy for carpark left zone b was (count(customerid)/30)= 3/30
Hey @ninakarsa ,
to avoid any confusion about the date format, upload a pbix that contains sample but still represents your data model to onedrive or dropbox and share the link, if you use xlsx file(s) to create the sample data, upload these file(s) as well.
Why is table B calculated, and what is the source for this calculation?
Regards,
Tom
Thanks for your prompt reply, now say i want to be able to drill on car park and car park area to get the final results how can this be done. Bookings table below
Booking Data:
Customer ID | adate | ddate | car park | car park area |
123 | 01/05/2019 | 05/05/2019 | Left | Zone A |
127 | 01/05/2019 | 03/05/2019 | Left | Zone B |
130 | 01/05/2019 | 03/05/2019 | Left | Zone B |
133 | 01/05/2019 | 03/05/2019 | Left | Zone B |
124 | 02/05/2019 | 04/05/2019 | right | Zone B |
128 | 02/05/2019 | 04/05/2019 | Left | Zone C |
131 | 02/05/2019 | 04/05/2019 | Left | Zone C |
134 | 02/05/2019 | 04/05/2019 | Left | Zone A |
125 | 03/05/2019 | 05/05/2019 | Left | Zone C |
129 | 03/05/2019 | 05/05/2019 | Left | Zone A |
132 | 03/05/2019 | 05/05/2019 | Left | Zone A |
135 | 03/05/2019 | 05/05/2019 | right | Zone B |
126 | 04/05/2019 | 06/05/2019 | right | Zone A |
138 | 06/05/2019 | 08/05/2019 | right | Zone A |
136 | 06/05/2019 | 08/05/2019 | right | Zone C |
139 | 06/05/2019 | 08/05/2019 | left | Zone B |
137 | 06/05/2019 | 08/05/2019 | right | Zone A |
140 | 07/05/2019 | 09/05/2019 | left | Zone C |
143 | 09/05/2019 | 11/05/2019 | left | Zone C |
141 | 09/05/2019 | 11/05/2019 | left | Zone A |
Hey @ninakarsa
please explain the format of the date columns:
MM/DD/YYYY or DD/MM/YYYY
and also provide the details about the final outcome that you are looking for.
Regards,
Tom
Hey @ninakarsa
Much more important, take the time and provide date value in the sample data using the ISO format like so
YYYY-MM-DD, meaning October, 17th 2019 looks like this: 2019-10-17.
Sure this will take extra effort, but will avoid misunderstandings, consider this a kind act.
All other date values will lead to misunderstandings due to the regional settings of the international audience of this forum.
Regards,
Tom
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
42 | |
33 | |
30 | |
18 | |
18 |