I have to calculate the number of incidents based on time, last 7 hours, last 24 hours. It needs to be from the current time.
I need a calculation that does something like calculate total incidents of (current time) minus 7 hours.
I’ve added both the date and time table that i use.
EDNA Last 7 Hours.pbix (160.8 KB)
EDNA last 7 Hours.csv (52.4 KB)
Hello @chad.sharpe,
Thank You for posting your query onto the Forum.
In order to achieve the results based on the scenario that you’ve specified. Below is the measure alongwith the screenshot of the final results provided for the reference which calculates the results based on Incidents happenend in Last 24 Hours -
No. of Incidents =
CALCULATE( COUNTROWS( 'EDNA last 7 Hours' ) ,
FILTER( 'EDNA last 7 Hours' ,
DATEDIFF( 'EDNA last 7 Hours'[Opened Date and Time] , NOW() , HOUR ) <= 24 ) )
In Last 24 Hours, 16 incidents has happened which has been highlighted in the red box.
I’m also attaching the working of the PBIX file for the reference purposes.
Hoping you find this useful and meets your requirements that you’ve been looking for.
Important Note: Same method is applicable for Last 7 Hours as well as Last 8 Hours. The reason why I haven’t calculated that is because there’s no data for Last 7 or 8 Hours and therefore, it would have provided me with the results as Blanks. But you can apply it in your file, provided you’ve the data for very recent hours.
Thanks and Warm Regards,
Harsh
EDNA Last 7 Hours - Harsh.pbix (163.0 KB)
Hello @chad.sharpe,
In the file, I saw that you’re having both the type of columns inside the fact table i.e., one combined date/time column as well as individual columns for date and time. In case, you’ve large data model, in that case, it’ll bloat up the the size of the file. Rather you can eliminate the column “Opened Date and Time” from the table and use the individual date and time column to achieve the results. Below is the measure provided for the reference with a minor change in it -
No. of Incidents - v2 =
CALCULATE( COUNTROWS( 'EDNA last 7 Hours' ) ,
FILTER( 'EDNA last 7 Hours' ,
DATEDIFF( MIN( 'EDNA last 7 Hours'[Date] ) + MIN( 'EDNA last 7 Hours'[Time] ) , NOW() , HOUR ) <= 24 ) )
And since this measure will also provide the same results at a individual line item level as previous one. The only thing is, this new measure will not provide the totals at a Grand Total level which can be easily fixed by writing a small simple measure. Like this -
No. of Incidents - v2 - Totals =
SUMX(
ADDCOLUMNS(
SUMMARIZE(
'EDNA last 7 Hours' ,
'EDNA last 7 Hours'[Incident Number] ,
'Invoked Function'[Date] ,
'Time Table'[Time] ) ,
"@Totals" ,
[No. of Incidents - v2] ) ,
[@Totals] )
I’m also providing a link of an article from SQLBI as well as links of videos based on Best Practices in Power BI for reference purposes.
Hoping you find this useful!!
Thanks and Warm Regards,
Harsh
EDNA Last 7 Hours - Harsh v2.pbix (195.0 KB)
This is great… I’ll test it out today.
My dataset comes with the Date & Time column and i break it out into date and then time for the measures. I usually keep the Date & Time column so as to see the original column incase i need to trouble shoot.
Do you think that one column consumes enough memory to remove it?
Hello @chad.sharpe,
It depends upon how much volume of data you’ve inside the Fact table (in terms of rows as well as columns). The file which you’ve shared contains only 1,944 rows and when I deleted the date/time column, the file size reduced from 196KB to 187KB.
Although, the difference right now is very minimal but just imagine that by removing that column, the file size got reduced by 9KB’s. If you’ve data in large volumes that’s in thousands or millions or rows then it’ll start to have quite a huge impact on the model.
Thanks and Warm Regards,
Harsh
That’s a good point, I’m going to start removing the unwanted columns.