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)
VijayP's avatar
VijayP
Community Champion
5 years ago

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

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.