Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a table which has records of maintenance jobs completed at rented houses. Each record has (amongst other things):
Created On Date
Completed Date
Priority (associated SLA for that priority).
I have various calculated columns already which show me:
For jobs that do not have a completed date (i.e are still outstanding), how many days over their SLA target they are
For completed jobs, how many days after their SLA they were completed on.
What I now want to see, is, (I'm not sure what you would use? A slicer or a measure etc..) but I would like to see, on a certain date in the past, how many jobs were live (i.e not completed) and overdue on that day.
I know I have all of the data, I just don't know what to do to visulise this.
Further to this I would like to be able to compare this for example to the same month the previous year, or the previous month etc.
Thanks for your help
Lottie
Solved! Go to Solution.
Hi @lottieritchie Thanks for your description. I create a new measure to count the live jobs. It works when you select a continuous period of time (month, week, quarter) or a specific date. Here is the PBIX file.
Live jobs 2 =
VAR _periodStart = MIN ( Dates[Date] )
VAR _periodEnd = MAX ( Dates[Date] )
RETURN
CALCULATE (
COUNT ( 'Table'[Job] ),
FILTER (
ALL ( 'Table' ),
NOT (
'Table'[Created Date] > _periodEnd
|| (
'Table'[Completed Date] < _periodStart
&& NOT ( ISBLANK ( 'Table'[Completed Date] ) )
)
)
)
)
Regards,
Jing
Additionally, if you want to get the result of last period or the same period last year, you can change the variables _periodStart and _periodEnd in above measure. For example:
I'm still having trouble with this issue if anyone is able to help? Many thanks
It seems there are several issues to deal with. Let's start with selecting a certain date.
Assume you have data like above, you can create below measure to calculate the number of live jobs on a selected date. I add an independent Date table in the model. You can use this Date table to select a date in a slicer. Here is the pbix file .
Live jobs =
VAR _selectedDate = SELECTEDVALUE ( Dates[Date] )
RETURN
CALCULATE (
COUNT ( 'Table'[Job] ),
FILTER (
ALL ( 'Table' ),
'Table'[Created Date] <= _selectedDate
&& (
'Table'[Completed Date] > _selectedDate
|| ISBLANK ( 'Table'[Completed Date] )
)
)
)
My question is that, if a job was created on 15th March 2019 and completed on 10th April 2019, when you want to count the number of live jobs at month level, should it be counted as a live job in March 2019 or in April 2019 or other result? How to deal with this condition? Also how to deal with a job only lasting for several days in the same month?
Regards,
Jing
Hi Jing,
Thanks so much for your response. And that you for the formula.
They are very good questions...
In your example I think I would want to count a live job in both March and April ideally, to show that the team were managing a job both in March and in April. Same for if the job only lasted for several days, I would still want to count that as a live job for that month.
Sorry one more question, now I have created that formula (I have a date table in my data already), I have added a filter to the page pulling in the date, but it doesn't seem to effect my, in the case 'card' which I have displaying the Live Jobs count. Could you advise what I may be doing incorrectly? Many thanks again for your help!
Lottie
Sorry my slicer is working, but as you say, only if I select a specific day, rather than being able to select a month, or a week.
Am I able to use this new Live Jobs field to compare trends etc, as opposed to just using a slicer to look at specific dates?
Many thanks
Hi @lottieritchie Thanks for your description. I create a new measure to count the live jobs. It works when you select a continuous period of time (month, week, quarter) or a specific date. Here is the PBIX file.
Live jobs 2 =
VAR _periodStart = MIN ( Dates[Date] )
VAR _periodEnd = MAX ( Dates[Date] )
RETURN
CALCULATE (
COUNT ( 'Table'[Job] ),
FILTER (
ALL ( 'Table' ),
NOT (
'Table'[Created Date] > _periodEnd
|| (
'Table'[Completed Date] < _periodStart
&& NOT ( ISBLANK ( 'Table'[Completed Date] ) )
)
)
)
)
Regards,
Jing
Additionally, if you want to get the result of last period or the same period last year, you can change the variables _periodStart and _periodEnd in above measure. For example:
Thank you so much this is great. 👍
I have assumed some Data based on your explanation and created this pbix . Look at this and let me know if it helps
Proud to be a Super User!
Hi, thanks for your help. I'm not quite there on what I need yet.
Thanks for the sample data, I have just swapped the Open/Close around so those without a closed date now show as Open.
But using this as an example, on Tuesday 14th April, 2020, Project 17 is open, and project 21 is still also open. So on that day, 2 projects are currently open.
On the 14th April 2019 Project 11 & Project 13 would have been open on that day. So what I would like to see is something showing me 2 open on that day this year, and 2 open on the same day the previous year (and I would like to be able to change this to look at whole months/years etc?
Hope that makes sense? Thanks for your help.
@lottieritchie , refer my HR blog, it also deals with a similar issue
Hi, thank you very much this is very helpful. I have replicated most of these fields, other than I have got stuck on one where it is not liking the second _min_date in my expression:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |