Measure to find double object in one week

[IAM]

Not sure if it is possible. I'm afraid not...

Is there a way, with a measure, to determine double objects in the same week.

So, I have a table (Sheet1) that stores workorders on different objects [Object].

If the workorder [Workorder] has a different workorder on the same object[Object], in the 7 days before or after that date [Date created], I want to return a YES, other a NO.

It really has to be a measure, not a calculated column or with Power Query.

Is it somehow possible to create this? 

[Anonymous]

Hi  @IAM ,

Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

Best Regards

[IAM]

These measures doesn't work. I think I wasn't clear enough.

The workorder doesn't have a date. It is just a code.

I need to compare the date created of different workorders (so different rows in a table)

[Anonymous]

Hi @IAM ,

How to compare the created data of different workorders? If you have the data in below table, could you please provide the related logic on it and your final expected result? Thank you.

I update the formula of measure as below and check if that is what you want. Please find the details in the attachment.

MFeasure =
VAR _selobj =
    SELECTEDVALUE ( 'object'[Object] )
VAR _selcdate =
    SELECTEDVALUE ( 'object'[Date Created] )
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'object'[Workorder] ),
        FILTER ( ALLSELECTED ( 'object' ), 'object'[Object] = _selobj )
    )
VAR _predate =
    CALCULATE (
        MAX ( 'object'[Date Created] ),
        FILTER (
            ALLSELECTED ( 'object' ),
            'object'[Object] = _selobj
                && 'object'[Date Created] < _selcdate
        )
    )
RETURN
    IF (
        _count > 1
            && (
                DATEDIFF ( _predate, _selcdate, DAY ) < 7
                    || DATEDIFF ( _predate, _selcdate, DAY ) > 7
            ),
        "Yes",
        "No"
    )

Best Regards

[Manoj_Nair]

@IAM If you see the DAX I have not used the Workorder. The DAX uses [Object] and [Date Created] only

IsRepeatObject = 
Var currobject = SELECTEDVALUE(Sheet1[Object])
Var currdate = SELECTEDVALUE(Sheet1[Date Created])
Var result = if (countx(filter(all(Sheet1), Sheet1[Object]=currobject && currdate >= Sheet1[Date Created]-7 && currdate <= Sheet1[Date Created]), Sheet1[Object])>1, "YES","NO")
return result

[Anonymous]

Hi @IAM ,

You can create a measure as below to get it, please find the details in the attachment.

Measure = 
VAR _selobj =
    SELECTEDVALUE ( 'object'[Object] )
VAR _selwo =
    SELECTEDVALUE ( 'object'[Workorder] )
VAR _selcdate =
    SELECTEDVALUE ( 'object'[Date Created] )
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'object'[Workorder] ),
        FILTER ( ALLSELECTED ( 'object' ), 'object'[Object] = _selobj )
    )
RETURN
    IF (
        _count > 1
            && DATEDIFF ( _selwo, TODAY (), DAY ) <= 7
            && _selcdate < _selwo,
        "Yes",
        "No"
    )

Best Regards

[Manoj_Nair]

@IAM check if this solution works for you. Thanks

If this post helps, then please consider to Accept it as the solution

[Greg_Deckler]

@IAM Try:

Measure = 
  VAR __Object = MAX(object[Object])
  VAR __DateMax = MAX(object[Date]) - 1
  VAR __DateMin = __Date - 7
  VAR __Table = SELECTCOLUMNS(FILTER('Table', [Date] >= __DateMin && [Date] <= __DateMax),"__object",[Object])
  VAR __Result = IF(__Object IN __Table, "YES", "NO")
RETURN
  __Result
  

[IAM]

Thanks!

Can it be something is not quite right @Greg_Deckler ?

These gives errors:

MAX(object[Object])

should I replace the first 'object with the name of my table?

VAR __DateMin = __Date - 7 

__Date is nothing right?

Measure = 
  VAR __Object = MAX(object[Object])
  VAR __DateMax = MAX(object[Date]) - 1
  VAR __DateMin = __Date - 7
  VAR __Table = SELECTCOLUMNS(FILTER('Table', [Date] >= __DateMin && [Date] <= __DateMax),"__object",[Object])
  VAR __Result = IF(__Object IN __Table, "YES", "NO")
RETURN
  __Result

[Greg_Deckler]

@IAM Sorry, I changed a variable name and yes, that should be your table name.

Measure = 
  VAR __Object = MAX('Table'[Object])
  VAR __DateMax = MAX('Table'[Date]) - 1
  VAR __DateMin = __DateMax - 7
  VAR __Table = SELECTCOLUMNS(FILTER('Table', [Date] >= __DateMin && [Date] <= __DateMax),"__object",[Object])
  VAR __Result = IF(__Object IN __Table, "YES", "NO")
RETURN
  __Result