Activity analysis for Gaming machines

[PaulBoyes]

Hi

One of the markets we are involved in is the amusement / gaming machine market (slot machines in pubs, clubs, arcades, casinos etc) and we have devices that are plugged into each machine and we capture absolutely every activity on the machines (Cash in / out, games played etc etc). This data is captured down to the second for each activity.

We already use Power BI for financial analyses but we would like to do some time based analysis. i.e Time of day in hourly bands, days of the week, weeks of the month etc to give our customers better insights as to sites, machine types and the playing / profitability patterns.

Most of your courses looking at Time Intelligence seem to be slanted to financial matters.

Can you recommend specific elements where there is an activity focus.

Best regards

Paul Boyes

[ctvv3010]

I’d suggest creating a date table, then adding DAX formulas for specific days of week, or weeks of the month like you wish. One example is in here.

[JarrettM]

Definitely going to need a proper Date Table in order to do those time intelligence functions. Once you get a Date Table setup and have some questions on your specific Measure(s), we will be glad to help. Here is a link to a recent post that has some good info on how to setup a proper date table.

Calculate totals, pr week without a date in table

Hi, this Works well. But, i run into another problem. In the RevenueBudget data there is 53 weeks, not 52. Week 53 is the last days in december, when the January 1st is in the middle of the week. Any experience how to handle week 53 in a scenario like this ?

Thanks
Jarrett

[marcster_uk]

Depending of your specifics, I thought I would post here, one way to create a date and hour table. The below creates a table with dates, hours, week numbers, days of week etc… from 1st Jan 2019 to 31st Dec 2020.

In Modeling view press, New table
Copy this into the formula bar:

DateTimeTable = 
ADDCOLUMNS (
    CROSSJOIN (
        CALENDAR ( DATE ( 2000, 1, 1 ), DATE ( 2025, 12, 31 ) ),
        UNION (
            ROW ( "Time", TIME ( 1, 0, 0 ) ),
            ROW ( "Time", TIME ( 2, 0, 0 ) ),
            ROW ( "Time", TIME ( 3, 0, 0 ) ),
            ROW ( "Time", TIME ( 4, 0, 0 ) ),
            ROW ( "Time", TIME ( 5, 0, 0 ) ),
            ROW ( "Time", TIME ( 6, 0, 0 ) ),
            ROW ( "Time", TIME ( 7, 0, 0 ) ),
            ROW ( "Time", TIME ( 9, 0, 0 ) ),
            ROW ( "Time", TIME ( 10, 0, 0 ) ),
            ROW ( "Time", TIME ( 11, 0, 0 ) ),
            ROW ( "Time", TIME ( 12, 0, 0 ) ),
            ROW ( "Time", TIME ( 13, 0, 0 ) ),
            ROW ( "Time", TIME ( 14, 0, 0 ) ),
            ROW ( "Time", TIME ( 15, 0, 0 ) ),
            ROW ( "Time", TIME ( 16, 0, 0 ) ),
            ROW ( "Time", TIME ( 17, 0, 0 ) ),
            ROW ( "Time", TIME ( 18, 0, 0 ) ),
            ROW ( "Time", TIME ( 19, 0, 0 ) ),
            ROW ( "Time", TIME ( 20, 0, 0 ) ),
            ROW ( "Time", TIME ( 21, 0, 0 ) ),
            ROW ( "Time", TIME ( 22, 0, 0 ) ),
            ROW ( "Time", TIME ( 23, 0, 0 ) ),
            ROW ( "Time", TIME ( 24, 0, 0 ) )
        )
    ),
    "DateTime", [Date] + [Time],
    "Hour", HOUR ( [Time] ),
    "WeekNumber", WEEKNUM([Date],2),
    "Day",WEEKDAY([Date],2),
    "WeekOfYear",WEEKNUM([Date],2),
    "DayofMonth", DAY([Date])
)

As has hours of the day, the date field will have 24 rows for each date.
Format the columns, i.e. Time column shows date/month/year, so format as Time…

[marcster_uk]

Script for Creating TIME Table in Power BI with Hours, Minutes and Seconds...

[PaulBoyes]

Hi

Thank you for the replies, much appreciated.

Once again very helpful and improving my knowledge.

Paul