Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Time line with data?

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?

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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!

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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!