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

        Thank you so much this is great. 👍