sql code in Dax

[ninakarsa]

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

[TomMartens]

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

[ninakarsa]

@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

[TomMartens]

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

[ninakarsa]

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

@TomMartens

[TomMartens]

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]

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