Time line with data?

[Anonymous]

Hi!

I need to make a timeline, and count how many rows that are "active" for each hour.. 

Today i have a table with

RowID, Startdatetime, Enddatetime

I also have a table with dates, and a table with hours. 

How can i utilise these to make a timeline for simultaniously active "rows"?

In T-SQL i have constructed something with "Cross Apply" and WITH statements.. But this takes loooads of time even with quite small amount of data.. Can this be handled better directly in PowerBI?

[Anonymous]

Hi @Anonymous 

I apologise if i haven't quite grasped your description full and this is a bit off the cuff but...

What about adding a column to your table which is case 1 or 0 depending on the relevant hour and it being active or not.

Then you should be able to do a count in Power BI?

[Anonymous]

Hi there!

It might be me being bad at explaining. 🙂

To do the same thing in T-SQL i had to do like in the script here. 

I "loop" through days, and then the hours on those days, and for each "row" of day and number, i do a count script on the posts that is between those datetimes.. 

It is this script that i feel that powerbi might be able to fix more elegant. 

SELECT 
cast(d.DATUM as date) Datum, 
cast(CONCAT(B.TIMME,':00') as time) Timme, 
Antal
FROM D
CROSS APPLY (
	SELECT TIMME,
	Antal
	FROM T T
	CROSS APPLY 
	   ( 
			SELECT
			(
				SELECT count(*) 
				FROM vTF
				WHERE 
				vTF.KONTAKTSTARTDATUM <= CAST(CONCAT(convert(date,D.DATUM),' ',T.TIMME,':00') as datetime) -- Kontakten startad 
				AND vTF.KONTAKTSLUTDATUM > dateadd(hour, +1, CAST(CONCAT(convert(date,D.DATUM),' ',T.TIMME,':00') as datetime)) -- Men ej avslutad
			) as Antal
	   ) A
) B
ORDER BY D.DATUM, TIMME

The output from this script would be. 

DATUM, TIMME, Antal

2019-10-25, 12:00, 5

2019-10-25, 13:00, 7

2019-10-25, 14:00, 4

[Anonymous · Accepted solution]

I have found a great solution with DAX in Power BI. Will try to walk you through it: 

1. I have a table with data, that has a "startdatetime" and "enddatetime". And i want to make a historical view of these, and count how many is "active" each hour. 

2. I create a table for date and time structure like this (as an example i found on this forum):

Dates = SELECTCOLUMNS(
    CROSSJOIN(
        
        //************************************************************
        // Date range below. This will generate a table with a [Date] column
        //************************************************************
        CALENDAR(min(LivedataAkuten[KONTAKTSTARTDATUM]);max(LivedataAkuten[KONTAKTSTARTDATUM]));

        //************************************************************
        // Time granduality below. This will generate a table with a [Time] column
        //************************************************************
        SELECTCOLUMNS(
            CROSSJOIN(
                SELECTCOLUMNS(GENERATESERIES(0;0;1);"Second";[Value]);
                SELECTCOLUMNS(GENERATESERIES(0;0;1);"Minute";[Value]);
                SELECTCOLUMNS(GENERATESERIES(0;23;1);"Hour";[Value])
            );
            "Time"; TIME([Hour];[Minute];[Second])
        )
    );
    //************************************************************
    // Custom table columns below. 
    // This is extracted from the Date and Time columns generated above
    //************************************************************
    "Date"; FORMAT([Date]; "YYYY-MM-DD");
    "Time"; FORMAT([Time]; "hh:mm:ss");
    "DateTime"; [Date]+[Time]
)

3. I create a new table with dates, and groupinginformation like this

AktivaSamtidigt = 
CROSSJOIN(Dates;DISTINCT(Table[GroupOfAccounts]))

4. In that table i add a column with this script

Antal = 
VAR Antalet = COUNTX(FILTER(Table;Table[STARTDATE]<=AktivaSamtidigt[DateTime]&&Table[ENDDATE]>AktivaSamtidigt[DateTime]+TIME(1;0;0)&&Table[GroupOfAccounts]=AktivaSamtidigt[GroupOfAccounts]);Table[ContactID])
RETURN
IF(ISBLANK(Antalet); 0; Antalet)

From this I can then create a visual presentation like this:

This is working way faster than scripting with the crossjoin as I did before!

So! That's a good friyay evening!

[Anonymous]

Great going!