Forum Discussion

Keshavpooja's avatar
Keshavpooja
Frequent Visitor
3 years ago

Find missing timestamp in time series data

Hi Experts,

I have a timestamp field that is filled for every minutes of a day coming in a fact table along with some other fields, so therotically there should be 1440 rows(60*24) per day. But practically few minutes will be skipped in the source, for instance, the below example entries have missing timestamp for 09:26, 09:27, 09:30, 09:31, 09:32, 09:37. 

2022-03-07 09:23

2022-03-07 09:24

2022-03-07 09:25

2022-03-07 09:28

2022-03-07 09:29

2022-03-07 09:33

2022-03-07 09:34

2022-03-07 09:35

2022-03-07 09:36

2022-03-07 09:38

2022-03-07 09:39

 I need to calculate %  of missing entries measure i.e. "total number of missing time entries/total time entries available". 

Could you pls guide me the right approach for this along with the DAX.

Need Help

2 Replies

  • Ahmedx's avatar
    Ahmedx
    Super User
    3 years ago

    You need to create a special calendar

    then and create 3 measures to calculate

     

     

    total number of missing time entries = 
VAR _missingTime = EXCEPT ( 'Date', 'tbl' ) RETURN COUNTROWS ( _missingTime )

---
total time entries available = 
COUNTROWS ( 'tbl' )

--
% Time = 
DIVIDE (
    [total number of missing time entries],
    [total time entries available]
)

     

     

     download an example
    https://dropmefiles.com/vyUEK