Forum Discussion

charcoal's avatar
charcoal
Regular Visitor
2 years ago
Solved

Retrieving value from earlier row with multiple filters

Major novice here, trying to retrieve usage values from a previous date of the same Product/Color/Machine to use to calculate change from previous day. Trying to highlight any major change from previ...
Need Help
  • danextian's avatar
    danextian
    2 years ago

    Please try this:

     

    Last Val = 
VAR LastStartTime =
    CALCULATE (
        MAX ( 'Table'[Start Time] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Product], 'Table'[Color], 'Table'[Machine] ),
            'Table'[Start Time] < EARLIER ( 'Table'[Start Time] )
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Usage] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Product], 'Table'[Color], 'Table'[Machine] ),
            'Table'[Start Time] = LastStartTime
        )
    )
danextian's avatar
danextian
Icon for Super User rankSuper User
2 years ago

Hi charcoal

Try this:

Last Val = 
    CALCULATE (
        MAX ( 'Table'[Usage] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Product], 'Table'[Color], 'Table'[Machine] ),
            'Table'[Start Time] < EARLIER ( 'Table'[Start Time] )
        )
    )

 

  • charcoal's avatar
    charcoal
    Regular Visitor
    2 years ago

    Thanks danextian 

     

    However it looks like the result is still out of sync with the previous rows for that combo, showing the same value over and over again.

     

     

     

    One thing to note, the sample set shown here only shows one product/color combo, but the full data set is an array of many Product #'s and Color #'s. Larger example set including multiple products and colors below:

     

    Start TimeProductColorMachineUsage
    6/4/2024387187B1126
    6/4/2024387187B2137
    6/4/2024387187B3138
    6/4/2024387187B493
    6/4/2024387194B1144
    6/4/2024387194B2130
    6/4/2024387194B3130
    6/4/2024387194B479
    6/3/2024220187B1105
    6/3/2024220187B261
    6/3/2024220187B384
    6/3/2024220187B4114
    6/3/2024387194B1146
    6/3/2024387194B2131
    6/3/2024387194B3131
    6/3/2024387194B478
    5/31/2024220194B1142
    5/31/2024220194B242
    5/31/2024220194B350
    5/31/2024220194B4120
    5/22/2024387187B1126
    5/22/2024387187B2138
    5/22/2024387187B3137
    5/22/2024387187B494
    5/21/2024387187B1125
    5/21/2024387187B2138
    5/21/2024387187B3137
    5/21/2024387187B494
    5/21/2024387194B1145
    5/21/2024387194B2131
    5/21/2024387194B3130
    5/21/2024387194B480
    5/20/2024387187B1125
    5/20/2024387187B2139
    5/20/2024387187B3136
    5/20/2024387187B493
    5/14/2024220187B1108
    5/14/2024220187B283
    5/14/2024220187B385
    5/14/2024220187B4113
    5/13/2024387187B1128
    5/13/2024387187B2138
    5/13/2024387187B3137
    5/13/2024387187B494
    5/10/2024220187B1107
    5/10/2024220187B274
    5/10/2024220187B385
    5/10/2024220187B4111
    5/10/2024220194B1147
    5/10/2024220194B254
    5/10/2024220194B361
    5/10/2024220194B4125
    5/6/2024387187B1127
    5/6/2024387187B2138
    5/6/2024387187B3137
    5/6/2024387187B493
    5/3/2024387194B1148
    5/3/2024387194B2132
    5/3/2024387194B3131
    5/3/2024387194B479
    5/1/2024220187B1107
    5/1/2024220187B285
    5/1/2024220187B388
    5/1/2024220187B4113
    4/30/2024220187B1107
    4/30/2024220187B273
    4/30/2024220187B385
    4/30/2024220187B4113
    4/24/2024387187B1128
    4/24/2024387187B2138
    4/24/2024387187B3137
    4/24/2024387187B494
    4/23/2024387194B1148
    4/23/2024387194B2131
    4/23/2024387194B3131
    4/23/2024387194B479
    4/20/2024220194B1145
    4/20/2024220194B256
    4/20/2024220194B361
    4/20/2024220194B4125
    4/16/2024220187B1106
    4/16/2024220187B276
    4/16/2024220187B383
    4/16/2024220187B4111
    4/15/2024387187B1128
    4/15/2024387187B2137
    4/15/2024387187B3137
    4/15/2024387187B494
    4/15/2024387194B1148
    4/15/2024387194B2132
    4/15/2024387194B3130
    4/15/2024387194B479
    4/13/2024220187B1106
    4/13/2024220187B278
    4/13/2024220187B384
    4/13/2024220187B4112
    4/11/2024387187B1127
    4/11/2024387187B2138
    4/11/2024387187B3138
    4/11/2024387187B494
    4/8/2024387187B1127
    4/8/2024387187B2137
    4/8/2024387187B3137
    4/8/2024387187B493
    4/4/2024387194B1147
    4/4/2024387194B2132
    4/4/2024387194B3131
    4/4/2024387194B478
    4/3/2024387194B1145
    4/3/2024387194B2130
    4/3/2024387194B3130
    4/3/2024387194B479
    4/2/2024387187B1126
    4/2/2024387187B2139
    4/2/2024387187B3138
    4/2/2024387187B493
    4/2/2024387194B1148
    4/2/2024387194B2132
    4/2/2024387194B3131
    4/2/2024387194B479
    4/1/2024387187B1129
    4/1/2024387187B2139
    4/1/2024387187B3137
    4/1/2024387187B495
    • danextian's avatar
      danextian
      Icon for Super User rankSuper User
      2 years ago

      Please try this:

       

      Last Val = 
VAR LastStartTime =
    CALCULATE (
        MAX ( 'Table'[Start Time] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Product], 'Table'[Color], 'Table'[Machine] ),
            'Table'[Start Time] < EARLIER ( 'Table'[Start Time] )
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Usage] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Product], 'Table'[Color], 'Table'[Machine] ),
            'Table'[Start Time] = LastStartTime
        )
    )
      • charcoal's avatar
        charcoal
        Regular Visitor
        2 years ago

        It looks like this worked perfectly, thank you. Definitely to expand my knowledge and use of variables. 

        Is there a simple way to calculate the rolling average from the previous 3 entries using this same general format?