Forum Discussion

lottieritchie's avatar
lottieritchie
Helper I
5 years ago
Solved

Time Intelligence

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 pr...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    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's avatar
    v-jingzhang
    5 years ago

    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)
v-jingzhang's avatar
v-jingzhang
Community Support
5 years ago

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's avatar
lottieritchie
Helper I
5 years ago

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's avatar
    lottieritchie
    Helper I
    5 years ago

    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's avatar
      v-jingzhang
      Community Support
      5 years ago

      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's avatar
        v-jingzhang
        Community Support
        5 years ago

        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)