Creating a visual to display the occupancy in a car park

DAX DAX Calculations
1

Hi,

I have a fact table with columns that show the Purchase Start Date & Time of a vehicle entering a car park and another column that shows the Purchase End Date & Time. I have also split these out into Start Date, Start Time, End Date and End Time.

I need to produce a visual that shows the total occupancy over a day, week, month etc, and so I also have a Date and Time dimension table.

I’ve tried bringing these values into a line chart, but with no success, so I would imagine that I need to create a measure of some kind. Any ideas?

I’ve attached some sample data covering two days.

Sample Data.xlsx (24.1 KB)

2

Hi @chrisgreenslade , we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.
  • Use the proper category that best describes your topic
  • Provide as much context to a question as possible.
  • Include the masked demo pbix file and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

3

Hello @chrisgreenslade,
Even though you didn’t provide us with a pbix file that explains better what u r trying to achieve. i gave it a try and i hope this answers your question.
You can add a calculated column to your fact table to calculate the occupency (difference between end time and start time) if that’s what u mean by occupency.
to calculate this column use the function

DATEDIF ( start date time, end date time, Hour/Minute…)

the 3rd parametrs gives you the possibility to calculate the difference either in Minutes, Hours…
Her’s a PBIX file. Hope that answers the question. if so, please mark this as a solution

4

I’m unable to see your PBIX file, @HASSAN_BI.

This helps to give me the purchase duration, but I need to be able to visualise the number of cars entering and leaving the car park at any one time. This is what I mean by occupancy.

5

Hi @chrisgreenslade,
My aplogies !

Sample data.pbix (91.8 KB)

6

Hi @HASSAN_BI.

Thanks for the file. What you have calculated is the total time duration for each vehicle that was in the car park that day.

However, what I need to do is work out the number of cars entering and leaving the car park over a period of time. For example, if 30 cars occupy the car park at 09:15 and then 2 were to leave at 09:17, the occupancy would be 28 at this time. A further 7 cars could then enter at 09:19 bringing the occupancy up to 35.

This is what I need to calculate. @Melissa are you or anyone else able to help too?

7

Hi @chrisgreenslade,

See if this works for you.

image
image745×370 14.5 KB

image
image653×602 9.34 KB

I hope this is helpful.

8

I wonder if I have entered the DAX incorrectly, but I get this error.

image
image1920×1080 280 KB

9

It’s a Measure, working example attached.

Sample data.pbix (93.2 KB)

1 Like
10

Thanks.

I am writing it as a measure, but still getting the same error. Very odd.

11

Hi @Melissa,

I’m not sure this is a working example. See attached.

image
image1920×1080 233 KB

12

Okay I’m on the latest version of PBI, expect it’s related to this thread.

Try again but instead of the && use a comma like below

Occupancy = 
VAR TimeSelection = SELECTEDVALUE( Data_Sample[Purchase Start Date & Time] )
RETURN

COUNTROWS( 
    CALCULATETABLE( Data_Sample,
        REMOVEFILTERS( Data_Sample[Purchase Start Date & Time] ),
        Data_Sample[Purchase Start Date & Time] <= TimeSelection,
        Data_Sample[Purchase End Date & Time] >= TimeSelection
    )
)
13

Thank you @Melissa. That now works.

But how can I show this as a line graph over a 24 hour period say? The idea is that I can see a visual trend throughout the day.

14

I have tried

Occupancy running total =
CALCULATE(
[Occupancy],
FILTER(
ALLSELECTED(‘P&D Purchases’[Purchase Start Date & Time]),
ISONORAFTER(‘P&D Purchases’[Purchase Start Date & Time], MAX(‘P&D Purchases’[Purchase Start Date & Time]), DESC)
)
)

To see if I can get a running total of the occupancy measure, but with no success. Ideally I need to show a trend over a 24 hour period, week, etc.

15

Hi @chrisgreenslade,

Few things to keep in mind. First limit the number of questions in a single thread, you can always create a new topic. Furthermore it will be extremely helpful to you if you take some time and create a full mockup model that resambles your actual production file. I can promise you that will lead to more accurate- and quicker responses from forum members.

In your initial request you talked about visualizing these numbers in a line chart and I think this measure will allow you to do just that. Although keep in mind that because no model was provided, no dimensions were used…

  • Create a new report page
  • Select a Line Chart visual
  • Place the ‘P&D Purchases’[Purchase Start Date & Time] field on the Axis
  • and place the Occupancy measure in the Values section.

image
image1250×433 19.1 KB

.

1 Like
16

Ok @Melissa.

Thanks for your help. The visual is taking a long time to load and I am not sure it fully answers my question as you have zero occupancy between 18:00 and just after 06:00 the following day. Which is not the case in the sample data.

However, I have accepted it as a partial solution.

Thanks