Start/end date and time occupancy in a matrix table

[Anonymous]

Hello

I have a bookings table with a start date, end date, start time and end time, the dates sometimes span multiple days.

I am trying to create a matrix table, with date and time in the columns and category booking in the rows. I want to count the booking occupancies across date and time and conditional format (i know how to do this bit) so the user has a clear view of when there is a booking and when there is not.

I have previously used the below DAX for a different table where bookings start date and end date were the same. This however does not seem to work for this new table where start/end dates differ.

Court in Play =

var MinTime = min('Time table 2'[Time])

var MaxTime = max('Time table 2'[Time])

var MinDate = min('Date Table'[Date])

var MaxDate = max('Date Table'[Date])

var CourtsInPLay2 = COUNTROWS(FILTER(ALLSELECTED('Reports uvw_Event'),'Reports uvw_Event'[StartTime]<=MinTime && 'Reports uvw_Event'[EndTime]>MaxTime && 'Reports uvw_Event'[StartDate]=MinDate && 'Reports uvw_Event'[EndDate]=MaxDate))

Return

CourtsInPLay2

Any help would be most appreciated!

[v-yingjl · Accepted solution]

Hi @Anonymous ,

Sorry for replying late. Since you have a time table, you need to create a Date table and another calculated table to show the result:

Date Table = CALENDAR(MIN('Table'[Downtime start date]),MAX('Table'[Downtime end date]))

Final table = 
VAR tab =
    SUMMARIZE (
        CROSSJOIN (
            ADDCOLUMNS (
                CROSSJOIN ( 'Date Table', 'Time table' ),
                "A",
                    CONVERT ( [Date] & " " & [Time], DATETIME )
            ),
            ADDCOLUMNS (
                'Table',
                "Start",
                    CONVERT ( [Downtime start date] & " " & [Downtime strat time], DATETIME ),
                "End",
                    CONVERT ( [Downtime end date] & " " & [Downtime end time], DATETIME )
            )
        ),
        [Date],
        [Time],
        [A],
        [Count],
        [Start],
        [End]
    )
RETURN
    FILTER ( tab, [Start] <= [A] && [End] > [A] )

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tex628]

Hi @Anonymous , 

Give this a try:

Court in Play = 
var MinTime = min('Time table 2'[Time])
var MaxTime = max('Time table 2'[Time])
var MinDate = min('Date Table'[Date])
var MaxDate = max('Date Table'[Date])
var CourtsInPLay2 = 
COUNTROWS(
FILTER(ALLSELECTED('Reports uvw_Event'),
'Reports uvw_Event'[StartTime]<=MinTime && 
'Reports uvw_Event'[EndTime]>MaxTime && 
'Reports uvw_Event'[StartDate]<=MinDate && 
'Reports uvw_Event'[EndDate]>=MaxDate)
)
Return
CourtsInPLay2

[Anonymous]

Thanks for response. 

When i try this way, Its not filtering by category, and so all values in each row are the same?

[amitchandak]

@Anonymous , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

refer if this blog can help

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785

[Anonymous]

Hi, hope these screenshots help as a simple example?

[amitchandak]

@Anonymous , Create an hour table using generate series

Time = addcolumn ( generateseries(0,24) , "Time", time(0,[value],0))

And follow the steps in blog, place dates and date table m use start time and time and time table

In case you need a table, find the file attached after signature

[Anonymous]

Hi,

I have the hour by hour table, I can I incorporate this into the table, to allow me to drill down for an hour by hour view?

Thanks in advance

[Anonymous]

Thanks for your speedy responses!

I used the table2 dax in your attachment which has been helpful. Am i able to incorporate my time table into this expression as well?