Calculate changeover between products chronologically - difference between rows

[Anonymous]

Hallo all,

  i cannot find the way to do the following logic which is difference between rows, here is an example:

- the next Start date if a product in a certain machine is the same -   for example  for machine A firstdate (01.01) "apples", nextdate 16.02 "Apples" therefore  here  there is "no changeover"

- the next  condition if product in next date is different than "changeover" for example,  for Machine B, date: 20.08.  "carrots" and Machine B, date: 05.10. "chocholates" therefore there was a "changever.

is there a way to calculate the difference between rows chronologically when we have several machines ?

thank you all alot for the help ! 

Miguel

Order	Start date	Machine	Product	Changeover ( to be calculated)
123456	01.01.16	Machine A	Apples
234567	16.02.16	Machine A	Oranges	changeover
345678	03.04.16	Machine C	Pears	no changeover
456789	19.05.18	Machine B	Carrots	changeover
567900	04.07.16	Machine A	Oranges	no changeover
679011	20.08.16	Machine B	Carrots	no changeover
790122	05.10.16	Machine B	Chocolates	changeover
901233	20.11.16	Machine A	Oranges	no changeover
123440	05.01.16	Machine B	Chocolates	changeover
112345	21.02.16	Machine C	Pears
123456	08.04.16	Machine C	Apples	changeover
134567	24.05.16	Machine B	Carrots	no changeover
145678	09.07.16	Machine A	Oranges	no changeover

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a calculated column as below:

Changeover = 
VAR _predate =
    CALCULATE (
        MAX ( 'Table'[Start date] ),
        FILTER (
            'Table',
            'Table'[Machine] = EARLIER ( 'Table'[Machine] )
                && 'Table'[Start date] < EARLIER ( 'Table'[Start date] )
        )
    )
VAR _preprod =
    CALCULATE (
        MAX ( 'Table'[Product] ),
        FILTER (
            'Table',
            'Table'[Machine] = EARLIER ( 'Table'[Machine] )
                && 'Table'[Start date] = _predate
        )
    )
RETURN
    IF (
        ISBLANK ( _preprod ),
        BLANK (),
        IF ( _preprod = 'Table'[Product], "no changeover", "changeover" )
    )

Or you can create a measure as suggested by @speedramps :

Measure = 
VAR _seldate =
    SELECTEDVALUE ( 'Table'[Start date] )
VAR _selmac =
    SELECTEDVALUE ( 'Table'[Machine] )
VAR _selproduct =
    SELECTEDVALUE ( 'Table'[Product] )
VAR _predate =
    CALCULATE (
        MAX ( 'Table'[Start date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Machine] = _selmac
                && 'Table'[Start date] < _seldate
        )
    )
VAR _preprod =
    CALCULATE (
        MAX ( 'Table'[Product] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Machine] = _selmac
                && 'Table'[Start date] = _predate
        )
    )
RETURN
    IF (
        ISBLANK ( _preprod ),
        BLANK (),
        IF ( _preprod = _selproduct, "no changeover", "changeover" )
    )

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Anonymous]

Hi Rena ! 

Thank you very much, 

that worked great ! 

i have one small issue with the data, which is,  when the Orders repeat, i have repeated data sets were Changeover and No changeover can appear double. 

based on the Column results, can i make a measure that counts only "changeover" order"no changeover"  from distinct orders ?

Thank you ! 

Miguel 

[v-yiruan-msft]

Hi @Anonymous ,

You can create a measure as below to get it:

Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Order] ),
    'Table'[Column] IN { "changeover", "no changeover" }
)

Best Regards

[Anonymous]

Hallo,

thank you for the answer,  there is no "Select" in either Measure or Columns what function did you mean ?

kindest regards,

Miguel

[speedramps]

Hi yukipilas

Please consinder this solution, clcik the thumbs up and accept as solution button. Thank you   

Changeover =

VAR mydate= SELECT(table[startdate])
VAR myproduct = SELECT(table[product])

VAR prevdate=
CALCULATE(MAX(table[startdate]),
ALLEXCEPT(table[machine]),

table[startdate]) < mydate)

VAR prevproduct =

CALCULATE(SELECT(table[product],"Nonefound"),
ALLEXCEPT(table[machine]),

table[startdate]) = prevdate)

RETURN
SWITCH(TRUE(),
prevproduct = "Nonefound", BLANK(),
myproduct  = prevproduct, "no changeover",

"changeover"
)

😀

[Anonymous]

i  looked for a similar " Select"  but it doesnt seem to work,

Changeover =

VAR mydate= ALLSELECTED(Witz_vwKonfektionierung[StartDatum])

VAR myproduct = ALLSELECTED(Witz_vwKonfektionierung[Traegerbahnbreite])

VAR prevdate=

CALCULATE(MAX(Witz_vwKonfektionierung[StartDatum]),

ALLEXCEPT(Witz_vwKonfektionierung,Witz_vwKonfektionierung[LeoMat]),

Witz_vwKonfektionierung[StartDatum]) < mydate

VAR prevproduct =

CALCULATE(ALLSELECTED(Witz_vwKonfektionierung[Traegerbahnbreite]),"Nonefound",

ALLEXCEPT(Witz_vwKonfektionierung,Witz_vwKonfektionierung[LeoMat]),

Witz_vwKonfektionierung[StartDatum]) = prevdate

RETURN

SWITCH(TRUE(),

prevproduct = "Nonefound", BLANK(),

myproduct = prevproduct, "no changeover",

"changeover"

)

[speedramps]

Hi again yukipilas

I am so sorry for my typing mistake, I did it on my mobile phone instead of the using the laptop. 😀

It should have been SELECTEDVALUE. 😀😀

I think should work provising that you Date column is a Date type field.

If it is a text field then the < logic may give undesired results

Please consinder this solution, clcik the thumbs up and accept as solution button. Thank you   

Changeover =

VAR mydate= SELECTEDVALUE(table[startdate])
VAR myproduct = SELECTEDVALUE(table[product])

VAR prevdate=
CALCULATE(MAX(table[startdate]),
ALLEXCEPT(table[machine]),

table[startdate]) < mydate)

VAR prevproduct =

CALCULATE(SELECTEDVALUE(table[product],"Nonefound"),
ALLEXCEPT(table[machine]),

table[startdate]) = prevdate)

RETURN
SWITCH(TRUE(),
prevproduct = "Nonefound", BLANK(),
myproduct  = prevproduct, "no changeover",

"changeover"
)