Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
sport
New Member

Compare & Graph a series of tests that are composed of a start time, stop time,

I am a novice to Power BI, and Dax.   I could not find an example, or question related to my challenge.  I have a series of test as follows:

Test 1, date, time, value

Test 1, date, time, value

Test 1, date, time, value

Test 1, date, time, value

Test 2, date, time, value

Test 2, date, time, value

Test 2, date, time, value

Test 2, date, time, value

Test 3, date, time, value

Test 3, date, time, value

Test 3, date, time, value

Test 3, date, time, value

Test 3, date, time, value

Test 3, date, time, value

Test 3, date, time, value

Test 3, date, time, value

 

I want to graph the lapse time in hours from FIRSTDATE [date],[time] to LASTDATE [date],[time] of each test set, and then while using a single graph, plot each hour for each test, showing a comparison overtime to itself, as well as to the other test sets.

 

The first entry for each test set (Test 1, Test 2, Test 3) would have a lapse time of ZERO hours, as this entry is the starting point, the subsequencial entries would caculate the lapse time between the starting point, and the current entry date/time. 

 

I just cannot seem to an example or similiar question that demos my outcome.

 

1 REPLY 1
PC_PBI
Frequent Visitor


@sport wrote:

I am a novice to Power BI, and Dax.   I could not find an example, or question related to my challenge.  I have a series of test as follows:

Test 1, date, time, value

Test 1, date, time, value

Test 1, date, time, value

Test 1, date, time, value

Test 2, date, time, value

Test 2, date, time, value

Test 2, date, time, value

Test 2, date, time, value

Test 3, date, time, value

Test 3, date, time, value

Test 3, date, time, value

Test 3, date, time, value

Test 3, date, time, value

Test 3, date, time, value

Test 3, date, time, value

Test 3, date, time, value

 

I want to graph the lapse time in hours from FIRSTDATE [date],[time] to LASTDATE [date],[time] of each test set, and then while using a single graph, plot each hour for each test, showing a comparison overtime to itself, as well as to the other test sets.

 

The first entry for each test set (Test 1, Test 2, Test 3) would have a lapse time of ZERO hours, as this entry is the starting point, the subsequencial entries would caculate the lapse time between the starting point, and the current entry date/time. 

 

I just cannot seem to an example or similiar question that demos my outcome.

 


I used 2 calculated columns:

TestTime = length of each test

_CumulativeTestTime = cumulative sum of the individual test entries by Test set

TestResults TableTestResults Table

Use DateDiff to measure the time change

 

 
TestTime =
DATEDIFF (
CALCULATE (
MAX ( 'TestResults'[Date Time] ),
FILTER (
'TestResults',
'TestResults'[TestID] = EARLIER ( 'TestResults'[TestID] )
&& 'TestResults'[TableKey]
= EARLIER ( 'TestResults'[TableKey] ) - 1
)
),
'TestResults'[Date Time],
HOUR
)

_CumulativeTestDuration =

DATEDIFF (
    CALCULATE (
        MIN ( 'TestResults'[Date Time] ),
        FILTER (
            'TestResults',
            'TestResults'[TestID] = EARLIER ( 'TestResults'[TestID] )
        )
    ),
    'TestResults'[Date Time],
    HOUR
)

 

End Visualization

Final VisualizationFinal Visualization

 

 

 

 

Helpful resources

Announcements
PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.