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 previous iteration for that same Product/Color/Machine. Bear in mind, dates are not continous, some combos could be months between. 

 

I've tried the following based on some guidance from this forum, but I'm clearly not getting it, all I've been able to do is retreive values from the same row. LASTNONBLANK may not be necessary, records with no data are already filtered out of the dataset upon ingest. 

 

Last Val = CALCULATE(
    LASTNONBLANK(
        Usage[Usage],Usage[Usage]),
            filter(ALLEXCEPT(
                Usage,
                Usage[Usage],
                Usage[Start Time].[Date]),
                Usage[Start Time].[Date]<earlier (Usage[Start Time].[Date])
            ))
 
 
Below is what the data set looks like, filtered down a specific product and color, with records for 4 machines and their material usage. 
 
Start TimeProductColorMachineUsage
6/3/2024220187B1105
6/3/2024220187B261
6/3/2024220187B384
6/3/2024220187B4114
5/14/2024220187B1108
5/14/2024220187B283
5/14/2024220187B385
5/14/2024220187B4113
5/10/2024220187B1107
5/10/2024220187B274
5/10/2024220187B385
5/10/2024220187B4111
 
 
Below is what the target is with the "Last Val" column reflecting the last value for the combination of Product/Color/Machine from the previous date for that combo. For example,  6-3-2024/ product 220/ Color 187 /Machine B2 /Usage=61/ and last Val for product 220/ Color 187 /Machine B2 = 83 from the row for 5-14-2024
 
Start TimeProductColorMachineUsageLast Val
6/3/2024220187B1105108
6/3/2024220187B26183
6/3/2024220187B38485
6/3/2024220187B4114113
5/14/2024220187B1108107
5/14/2024220187B28374
5/14/2024220187B38585
5/14/2024220187B4113111
5/10/2024220187B1107107
5/10/2024220187B27485
5/10/2024220187B38588
5/10/2024220187B4111113
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
        )
    )

4 Replies

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