Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register 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! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
146 | |
75 | |
64 | |
52 | |
47 |
User | Count |
---|---|
218 | |
87 | |
71 | |
63 | |
60 |