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
(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)
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
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
|Customer ID||adate||ddate||car park||car park area|
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.
@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
|Car Park||Car Park Area||From Date||To Date||Spaces|
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?
Don't miss the Power BI Dev Camp this week!
Check out a full recap of the month!
Check out the winners of the recent 'Can You Solve These?' community challenge!
Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.