Time intelligence Measures

Hi everyone,

This is my first post on the forum and it would be great if i could get your help.

I am working on a Time Data Analysis. I retrieved data from an application used to conduct driving tests and I’m trying to develop these metrics/Visualisations:

  1. Total time spent from office write up to Journal:
    Point A = Pass/Fail/Test Summary Screen - These are the office write up categories
    Point B = Journal Event immediately after - This is the journal category

  2. Average time spent from office write up to Journal.

  3. March 2020 Vs June 2021 - Average times spent in office write up to journal.

  4. Total Tests by period - Show all tests conducted in March 2020 and June 2021

Unfortunately I am having issues with the data set, maybe its due to the way its set up? Or I’m possibly calculating the wrong measures.

I’ve attached the file for your reference, again if anyone could help that would be much appreciated. https://1drv.ms/u/s!AoTkCNSjWHozjh42H3wR-hi56pRY

Kind regards,

Hi @ThomasMatti, I noticed you didn’t provide a PBIX file. Providing one will help users and experts find a solution to your inquiry faster and better.

A perfect initial question includes all of the following:

  • A clear explanation of the problem you are experiencing

  • A mockup of the results you want to achieve

  • Your current work-in-progress PBIX file

  • Your underlying data file (to allow us to go into Power Query if necessary to transform your data and/or data model – often DAX questions really end up being data modeling solutions)

Check out this thread on Tools and Techniques for Providing PBIX Files with Your Forum Questions

Not completing your data may sometimes cause delay in getting an answer.

@ThomasMatti, it is very late here but I am curious and would like to explore this model more tomorrow. I don’t quite understand it though. I can’t find what columns reference Point A & Point B.
How you find the time for Office write up to journal? are they Test phase ID’s? it really isn’t clear.
How do you determine a test? Does it start with T1.0 and finish with T6.1? Can you do a count on T6.1?

1 Like

Hey KimC

Hope you’ve had a good day.

Apologies for the delayed response I’ve just seen this message late and have been working on this report quite heavily today. Good news I have actually updated the file and covered everything I need apart from one metric (The main metric).

So if possible what i need is this:

Average time it takes to go from point A to point B. - So Point A will either be Pass test summary screen or Fail test summary screen. Point B is Journal Screen. So ideally i need to measure the average time of how long it takes to go from Pass test summary screen to Journal screen and Fail test Summary screen to Journal Screen. Does this make sense? I think probably the best way to approach this is to have a drop down slicer where if you select Pass test summary screen it shows the average time it takes to go from pass test summary screen to journal screen and vice versa.

I have attached the newly updated file, again if you wanna ask any questions about the data just feel free to drop me a message.

Time Study Analysis - Office Write up.pbix (27.6 MB)

Kind regards,

1 Like

Apologies! Hopefully the new post covers everything and is a bit more clearer.

Actually i’ve just realised the averages are also completely wrong… Yeah if someone has the time to run through this data model with me that would be much appreciated.


Hi Thomas,

yes, much clearer thanks.

I am learning too
Time Study Analysis - Office Write up v1.pbix (27.7 MB)
so this was good practice for me :slight_smile:

I have added some measures into your model for you to check out, have a look at CountID and then Average Duration PassTest to Journal and Average Duration FailTest to Journal. I have also added Average PtA to PtB but not sure if this one is right.

Hi @ThomasMatti, did the response provided by @KimC help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

Hi @ThomasMatti, we’ve noticed that no response has been received from you since July 16th. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

Hi @ThomasMatti, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.