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 OrderStart date Machine ProductChangeover ( to be calculated)12345601.01.16Machine AApples 23456716.02.16Machine AOrangeschangeover34567803.04.16Machine CPearsno changeover45678919.05.18Machine BCarrotschangeover56790004.07.16Machine A Orangesno changeover67901120.08.16Machine BCarrotsno changeover79012205.10.16Machine BChocolateschangeover90123320.11.16Machine AOrangesno changeover12344005.01.16Machine BChocolateschangeover11234521.02.16Machine CPears 12345608.04.16Machine CAppleschangeover13456724.05.16Machine BCarrotsno changeover14567809.07.16Machine AOrangesno changeover

Anonymous

Not applicable

4 years ago

Solved

Calculate changeover between products chronologically - difference between rows

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

Need Help

Anonymous

4 years ago

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

Calculate changeover between products chronologically - difference between rows.pbix55 KB

7 Replies

speedramps
Super User
4 years ago
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
  Not applicable
  4 years ago
  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
    Super User
    4 years ago
    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"
    )
Anonymous
Not applicable
4 years ago
Hallo,
thank you for the answer, there is no "Select" in either Measure or Columns what function did you mean ?

kindest regards,
Miguel

Anonymous

Not applicable

4 years ago

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" )
    )

How to upload PBI in Community

Best Regards

Calculate changeover between products chronologically - difference between rows.pbix55 KB

Anonymous
Not applicable
4 years ago
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
- Anonymous
  Not applicable
  4 years ago
  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

Forum Discussion

Calculate changeover between products chronologically - difference between rows

7 Replies

Related content

Different schema into different destination

Sorting Date table chronologically

Sort years chronologically

sorting a date table chronologically

Date slicer Reverse chronology