Running count hourly from table with no timestamp

[Raaz]

Hi Community Experts,

I need to calculate count of a column on hourly basis from a tbale that has date but no time information/time stamp.

I read many artices, questions online. ALl mention the soloution from a table that already has tiem stamp.

My table is like this

ship_date         order#

04-1-2020        123

04-1-2020        124

04-1-2020        125

.........                  .....

04-1-202         134

What I want is, when user runs the report on 04-1-2020 at 10AM , count of order is diaplyed lets say 3

when run the report at 11AM, count of record is displayed say 7 like this

Time      Count of order

10AM      1

11AM      5

12AM     9

1PM     12

2PM     14

How this can be achieved. Any help will be appriciated

Thanks,

Raazia

What 

[kentyler]

I'm not clear about what you want.

Do you want the counts of orders to be updated whenever the user runs the report, so that it is always current and up to date ?

[Raaz]

Yes, current updated count with previuos count as well.

let say user runs the report at 9AM, output should be

9AM      3

at 10 AM when report is run, out pout should be

9AM    3

10AM   7

at 11AM, output should be

9AM   3

10AM   7

11AM   8

Thanks in advance

[kentyler]

Your problem is that power bi cannot easily write data, so recording the times your reports are run is tricky.

You could always run the reports at fixed hours of the day.... in which case your reports would be counting the records betwen different sets of predetermined hours.
Or you could take an approach like the one explained here https://www.youtube.com/watch?v=UFD5S4Peqc4  where a dax measure actually can write back to a sql database and record the time when a report is run...... for this to work you would have to refresh your data in power bi every time you wanted to re-run the report, so the report measures would have access to the record that tells the last time the report was run.

[Raaz]

Thanks for your help!!

I dont want to retrieve the time of my report run and writing it to sql.

I just want to run the report at fixed hour of the day and then counting the records between different sets of hours.

like I am able to retrieve the data like this

Time                countof orders

9AM               69

at 10 AM

9AM       60

10AM     60

at 11AM

9AM    57

10AM   57

11AM   57

(Report will be run at fixed hours, after every one hour)

My problem is , I am unable to retain the count for the prvious hours

I want output like this

9AM   69

10AM  60

11AM   57

is this possible ?

Please note my table doent have timestamp. I created a new calendar table with date and time and joined this date coulumn with date column of my table

Please help...

[kentyler]

The more I think about this, the more I think that you should really go back and arrange to get a timestamp added to your data. If someone wants to do a report BY HOUR, then its only reasonable that they need to provide a timestamp.