Running count hourly from table with no timestamp

Question

Hi Community Experts,&nbsp;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.&nbsp;My table is like this&nbsp;ship_date &nbsp; &nbsp; &nbsp; &nbsp; order#04-1-2020 &nbsp; &nbsp; &nbsp;&nbsp; 12304-1-2020 &nbsp; &nbsp; &nbsp;&nbsp; 12404-1-2020 &nbsp; &nbsp; &nbsp;&nbsp; 125......... &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; .....04-1-202 &nbsp; &nbsp; &nbsp; &nbsp; 134&nbsp;What I want is, when user runs the report on 04-1-2020 at 10AM , count of order is diaplyed lets say 3when run the report at 11AM, count of record is displayed say 7 like this&nbsp;Time &nbsp; &nbsp;&nbsp; Count of order&nbsp;10AM &nbsp; &nbsp;&nbsp; 111AM &nbsp; &nbsp;&nbsp; 512AM &nbsp; &nbsp; 91PM &nbsp; &nbsp; 122PM &nbsp; &nbsp; 14&nbsp;How this can be achieved. Any help will be appriciated&nbsp;Thanks,Raazia&nbsp;&nbsp;&nbsp;What&nbsp;

kentyler · Answer

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 · Answer

Yes, current updated count with previuos count as well.&nbsp;let say user runs the report at 9AM, output should be&nbsp;&nbsp;9AM&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3at 10 AM when report is run, out pout should be9AM&nbsp;&nbsp;&nbsp; 310AM&nbsp;&nbsp; 7&nbsp;at 11AM, output should be&nbsp;9AM&nbsp;&nbsp; 310AM&nbsp;&nbsp; 711AM&nbsp;&nbsp; 8&nbsp;Thanks in advance&nbsp;

kentyler · Answer

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&nbsp;https://www.youtube.com/watch?v=UFD5S4Peqc4&nbsp; 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 · Answer

Thanks for your help!!&nbsp;I dont want to retrieve the time of my report run and writing it to sql.&nbsp;I just want to run the report at fixed hour of the day and then counting the records between different sets of hours.&nbsp;like I am able to retrieve the data like this&nbsp;Time&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; countof orders9AM&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 69at 10 AM9AM&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6010AM&nbsp;&nbsp;&nbsp;&nbsp; 60&nbsp;at 11AM9AM&nbsp;&nbsp;&nbsp; 5710AM&nbsp;&nbsp; 5711AM&nbsp;&nbsp; 57&nbsp;(Report will be run at fixed hours, after every one hour)&nbsp;My problem is , I am unable to retain the count for the prvious hours&nbsp;I want output like this9AM&nbsp;&nbsp; 6910AM&nbsp; 6011AM&nbsp;&nbsp; 57&nbsp;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&nbsp;Please help...&nbsp;

kentyler · Answer

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.

Forum Discussion

Running count hourly from table with no timestamp

5 Replies

Related content

Running Maximum

datetime2 shows timestamp

Running sum

REAL TIME TILES AUTO CONVERT TIMESTAMP TO UTC

Pipeline not running