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)
Neeljy's avatar
Neeljy
Regular Visitor
1 year ago


You can achieve this using DAX measures rather than calculated columns, as they allow for dynamic filtering based on user selection.

Steps to visualize overdue live jobs on a past date:
Create a Date Table (if you don’t have one) and mark it as a Date Table in Power BI.
Create a Measure to Count Overdue Jobs on a selected date:
Overdue Live Jobs = VAR SelectedDate = SELECTEDVALUE('DateTable'[Date]) RETURN CALCULATE( COUNT('MaintenanceTable'[JobID]), 'MaintenanceTable'[Completed Date] = BLANK(), 'MaintenanceTable'[Created On Date] <= SelectedDate, 'MaintenanceTable'[SLA Target Date] < SelectedDate )
Use a Date Slicer to select any past date and see the overdue jobs count.
To compare with previous periods, create time intelligence measures like:


Overdue Live Jobs Last Year = CALCULATE([Overdue Live Jobs], SAMEPERIODLASTYEAR('DateTable'[Date])) Overdue Live Jobs Last Month = CALCULATE([Overdue Live Jobs], PREVIOUSMONTH('DateTable'[Date]))
Use a Line Chart or Table Visual to compare trends over time.