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

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. 👍