Forum Discussion

Lindserke's avatar
Lindserke
Regular Visitor
6 years ago
Solved

create empty dates / hours

Hi,    i'm quite new to Power BI, and i'm not really finding the right subject on the internet to help me. I have a large set of data, and would like to transform it as follows   AS IS: 01/01/2...
  • Lindserke's avatar
    Lindserke
    6 years ago

    i've found a bit of a solution :)!

     

    for those who have the same issue as me, I did the following

    - create date calendar: 

    https://kohera.be/blog/power-bi/how-to-create-a-date-table-in-power-bi-in-2-simple-steps/

     

    - create time calendar: https://kohera.be/blog/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/

    slightly adapted:

    TimeTable =
    VAR HourTable = SELECTCOLUMNS(GENERATESERIES(0, 23), "Hour", [Value])
    VAR MinuteTable = SELECTCOLUMNS(GENERATESERIES(0, 59), "Minute", [Value])
    VAR SecondsTable = SELECTCOLUMNS(GENERATESERIES(0, 59), "Second", [Value])

    RETURN
    ADDCOLUMNS(
    CROSSJOIN(HourTable, MinuteTable, SecondsTable),
    "Time", TIME([Hour], [Minute], [Second]),
    "TimeRound", Time([Hour],[Minute],0),
    "TimeKwartuur",time([Hour],(if ([Minute]<15,0,if ([Minute]<30,15,if ([Minute]<45,30,45)))),0)
    )

    - link tables:

     

    - create table using Date and Kwartuur from the created tables:

    i'm still struggling with the fact that he is only showing me  the hours where there was input (so for example, nothing before 6:30), but i'm already a bit more on track

     

    thanks for the advice, it put me on the right track !

amitchandak's avatar
amitchandak
Super User
6 years ago

Lindserke , I would recommend separate date and time and create date and time dimension table

 

Date = [Date time].date

Date = [Date time].time

 

https://kohera.be/blog/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

  • Lindserke's avatar
    Lindserke
    Regular Visitor
    6 years ago

    Hi amitchandak,

     

    thanks for the quick reply. Data is split by date and hour, i should have specified that one 🙂

     

    I indeed created calendar / time table as suggested by Kohera.be.

    but when i'm making a matrix, i'm only seeing the "used" dates/times, and not the empty ones... plus, i'm seeing the exact time, not the "kwartuur"

    maybe i'm doing something wrong with the connections?

     

    i'm not sure how to make this work.