I have an SQL data warehouse on my localhost. The data is loaded with an ETL project in SSIS. In the DWH I have a Logging table that gets filed every time an etl process runs. The logging table then gets reported in a dashboard in power bi. In the logging I have StartTime and EndTime (in datetime) of every etl process that ran. Some rows don't have an EndTime because the ETL failed there.
Now I am trying to make a measure in power bi that shows how long is the run time of each process in seconds. I've tried the following but it's not working:
TimeDiff = DATEDIFF(('Logging view'[StartTime]),('Logging view'[EndTime]), SECOND)
TimeDiff = Duration.TotalSeconds(('Logging view'[EndTime])-('Logging view'[StartTime]))
Anyone an idea on how to measure the runtime here?
When you use the DATEDIFF function it is expecting two dates, and if you use it like that in a measure, you're passing two columns.
My advice is to create a calculated column in you power bi model and use that formula:
TimeDiffSeconds = DATEDIFF(('Logging view'[StartTime]),('Logging view'[EndTime]), SECOND)
And then your measure can be the sum of the TimeDiffSeconds
TimeDiff = SUM('Logging view'[TimeDiffSeconds]
If you don't want to create a calculated column, you need to change the measure to give you only one date, and not a column, for example:
TimeDiff = DATEDIFF(MIN('Logging view'[StartTime]),MAX('Logging view'[EndTime]), SECOND)