March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
We have a simple table with visitor data that includes an arrival date/time and departure date/time, and we've charted the year's worth of data based on the hour of day, as shown in the attached image. This shows average arrivals per hour and average departures per hour. The average comes from a measure: Average Visitors = [Total Visitors]/[No. of Days], where Total Visitors is a record count, and No. of Days = DISTINCTCOUNT(Visitors[Arrival Date]). Each chart uses the same Average Visitors field. Is it possible to get these two lines on one chart? Or even just one line that showed the net by hour? If the latter, my guess is we'll have to add a measure to calculate that, but we are struggling with replicating what PowerBI is doing automatically in each chart.
Hi @Ryan19 ,
It is suggested to create another Dates table. And then create relationship between your fact table and the Dates table. Use the date/time hour column from Dates table as x-axis.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Share the link from where i can download your PBI file.
@Ryan19 , create a datetime table or create a date and time table (separately) and join that with arrival and departure. One will active another will be inactive. Activate other using userelation.
Time table
https://kohera.be/blog/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/
userelation
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-Y...
Appreciate your Kudos.
Many thanks for this response. I'm still working through it all and trying to make it work, and I appreciate you taking the time to post all of this information.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
129 | |
90 | |
75 | |
58 | |
53 |
User | Count |
---|---|
200 | |
104 | |
101 | |
67 | |
55 |