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