Time Intelligence

[lottieritchie]

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 

[v-jingzhang · Accepted 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

[Neeljy]

You can achieve this using DAX measures rather than calculated columns, as they allow for dynamic filtering based on user selection.

Steps to visualize overdue live jobs on a past date:
Create a Date Table (if you don’t have one) and mark it as a Date Table in Power BI.
Create a Measure to Count Overdue Jobs on a selected date:
Overdue Live Jobs = VAR SelectedDate = SELECTEDVALUE('DateTable'[Date]) RETURN CALCULATE( COUNT('MaintenanceTable'[JobID]), 'MaintenanceTable'[Completed Date] = BLANK(), 'MaintenanceTable'[Created On Date] <= SelectedDate, 'MaintenanceTable'[SLA Target Date] < SelectedDate )
Use a Date Slicer to select any past date and see the overdue jobs count.
To compare with previous periods, create time intelligence measures like:

Overdue Live Jobs Last Year = CALCULATE([Overdue Live Jobs], SAMEPERIODLASTYEAR('DateTable'[Date])) Overdue Live Jobs Last Month = CALCULATE([Overdue Live Jobs], PREVIOUSMONTH('DateTable'[Date]))
Use a Line Chart or Table Visual to compare trends over time.

[lottieritchie]

I'm still having trouble with this issue if anyone is able to help? Many thanks 

[v-jingzhang]

Hi @lottieritchie 

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

[lottieritchie]

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 

[lottieritchie]

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 

[v-jingzhang · Accepted 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

[v-jingzhang · Accepted solution]

@lottieritchie 

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:

• Last month

VAR _periodStart = EDATE(MIN(Dates[Date]),-1)

VAR _periodEnd = EDATE(MAX(Dates[Date]),-1)

• Same month last year

VAR _periodStart = EDATE(MIN(Dates[Date]),-12)

VAR _periodEnd = EDATE(MAX(Dates[Date]),-12)

[lottieritchie]

Thank you so much this is great. 👍

[VijayP]

@lottieritchie 

I have assumed some Data based on your explanation and created this pbix . Look at this and let me know if it helps

[lottieritchie]

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. 

[amitchandak]

@lottieritchie , refer my HR blog, it also deals with a similar issue

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[lottieritchie]

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: 

(Temp) Last Period Live Jobs =

var _min_date = minx(all(Calendar_Lookup,Calendar_Lookup[Date]),

var _expression =if(ISFILTERED(Calendar_Lookup[Month and Year]),MAXX(Calendar_Lookup,DATEADD(Calendar_Lookup[Date],-1,month)),maxx(Calendar_Lookup,DATEADD(Calendar_Lookup[Date],-1,year)))

Return

CALCULATE(COUNTX(filter(DB_Job_Data,DB_Job_Data[job_CreatedOn]<=_expression && DB_Job_Data[job_CreatedOn]>= _min_date && (Isblank(DB_Job_Data[job_ContractorAdvisedCompletedOn]) || DB_Job_Data[job_ContractorAdvisedCompletedOn]>_expression)),crossfilter(DB_Job_Data[job_CreatedOn],Calendar_Lookup[Date],none))))

Any idea what I am doing wrong here? 

Many thanks for your help.