Display Customers in Time Arrival Slots


I am trying to write a measure that will allow me to count how many customers purchased items at what time of the day. For example how many customers bought something between 9:00 AM and 10:00 AM.

I have a column in my table that has just times purchased:


I am also trying to understand trends on different days. So I have a date calendar I have linked to the table as well.

I thought about using the switch function but I am not having much luck with it.

Could anyone help?

Hi @matthew.wright,

Do you have a Time Dimension table in your Model?
If not here’s a topic on that. BTW you will probably want to cut the Time down to the Minute or Hour level if that is the granularity of reporting. Because that will have a huge impact on the cardinality of the column, model size and performance.


Thanks for sending this. I added a time table in addition to the date table I have. I tried to do a lookup value from the time a customer purchased to the period of the day to do counts but it gave me blanks.

I also still can’t seem to link specific hours either.

See below: Any thoughts?

test = LOOKUPVALUE(‘Invoked Function’[Period of Day],‘Invoked Function’[Time],Patient[Time_Created])


Returning blanks for me?

Yeah the granularity of the Time table is at the Minute level and your fact data at the Second so these don’t match… Like I suggested earlier you will probably want to cut the Time down to the Minute or Hour level if that is the granularity of reporting.That will have a huge impact on the cardinality and thus model size and performance.


I see now. Do you know if there is an easy way from the Query Editor to update my time to remove the seconds?

Hi @matthew.wright

Paste this into a new blank query, shows 2 options one for DateTime and one for Time without seconds.

    Source = DateTime.FixedLocalNow(),
    ConvertToTable = #table(1, {{Source}}),
    DateTime_NoSeconds = Table.AddColumn(ConvertToTable, "Custom", each DateTime.From ( DateTime.ToText( [Column1], "yyyy-MM-dd HH:mm") ), type datetime),
    TimeOnly = Table.AddColumn(DateTime_NoSeconds, "Time", each DateTime.Time([Column1]), type time),
    Time_NoSeconds = Table.AddColumn(TimeOnly, "Custom.1", each Time.From ( Time.ToText( [Time], "HH:mm") ), type time)

I hope this is helpful.


I appreciate all your help. I’m not sure how this query helps me. I want to convert the column in an existing table to remove the seconds from the time.

When I run this query it only gives me a single date and time:

Could you elaborate further? I am still a little new at this?

My apologies…

You have shown a “Time_Created” column that is of type time I would expect…
Go to that Query and on the “Add Column” tab select “Custom Column” a dialog box will be shown:


There you can enter this M code:
Time.From ( Time.ToText( [Time_Created], “HH:mm”) )

Finally in the formula bar (before the closing parenthesis) you can declare type, as shown here:

1 Like

That did the trick. Thank you so much!

1 Like