Forum Discussion
sql code in Dax
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
6 Replies
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
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
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
