Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'm trying to display a two-dimensional dataset. Basically when an event was created, and when it was finalised.
The two date stamps are in different tables. I am basically after a graph (or table) showing the following:
Date | Events created | Events finalised |
01/01/2020 | 5 | 4 |
01/02/2020 | 4 | 3 |
And so on. I can get a graph showing the events created, and a graph showing events finalised, but cannot seem to get two series on the one graph based on a different time.
For example, we may create an event on 5 January and finalise it on 10 January. Or create on 6 January and finalise on 10 January. If this were the simple case, a graph showing one series (events created) with two data points of one each, and a second series (events finalised) on the same graph showing a single data point with a count of two.
Any help would be appreciated.
Hi,
As long as there is a single Calendar Table which has a relationship with your base data table, this should be easy to create. I can offer specific help, if you share yoru data.
If both the dates are in a different table. and both have the event ID(Say). Then I would suggest having a common Date dimension and event dimension.
New Table
Event Dim = distinct(Table1[Event ID])
Have date dimension and join both created date and finalized date on this and use count of event measure to get output.
This will give on each date, month etc, how many events created and finalized. Now if you need when created and when finalized the use those dates itself but take either move them to event dim or have count from event dimension. In case you need date diff. Then refer: https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0
4 ways to take date diff. In this case order no is your event id. Order date is created date and Delivery Date is you finalize Date.
For calendar
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/
In case dates are in the same table. How to deal:https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin
So I think I'm getting there. I have a table called EventDim that has the application ID, the Received date, the finalised date and a fund ID:
rry, it's been a while and there's been this little global pandemic that has put a bit of a spanner in the works.
I have created some bubble charts using the LineDotChart visual:
It would be really cool to have the facility to have two series on the one chart. Failing that, having the two options in the one bar chart would be acceptable, but I haven't been able to get that to happen, because the received and the approved dates are in different tables. I have used the techniques described above and managed to get the figures for the organisation as a whoile, but have been unable to break them down by event (our organisation runs the same application process for multiple program owners, and the data needs to be able to be displayed per event, program or owner). I thought of building a date/event/applications matrix but it is beyond my capability.
Thanks for reading.
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |