J0bs on Site 3-6 months, 6-9 months

[Anonymous]

Hi I am currently trying to create KPIs using measures for jobs that are 0-3 months, 3-6 months etc

These came from week ending every friday, this spreadsheet is created from a sharepoint list with arrival date and completed date< i am trying to calculate these jobs on site: 0-3 months, 3-6 months I was given the defintion for these KPIs

Count of the number of Jobs which have an "actual arrival date" and a "Completed date" of after the end date of the report OR no "Completed date" populated. The arrival date should be between 91-180 days of the end date of the report, I tried the DAX code and had no luck

Jobs On Site 6-9 months = COUNTROWS(

        FILTER (

            'Job Tracker',

            (

                'Job Tracker'[Arrival Date] <> BLANK ()

                && (

                    ISBLANK ( 'Job Tracker'[Completed Date] )

                ) && 'Job Tracker'[Job Duration] < 271

            )

        )

    )+0

any suggestions would be appreciated thanks

[v-nuoc-msft · Accepted solution]

Hi @Anonymous 

I made some modifications based on the code you provided.

Here's some dummy data

“Job Tracker”

The modified code is as follows:

Jobs On Site 6-9 months = 
COUNTROWS(
    FILTER(
        'Job Tracker', 
        'Job Tracker'[Arrival Date] <> BLANK() 
        && 
        ISBLANK('Job Tracker'[Completed Date]) 
        && 
        'Job Tracker'[Arrival Date] >= TODAY() - 270 
        && 
        'Job Tracker'[Arrival Date] < TODAY() - 180
    )
)

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.