Previous rows value based on date (Lag functionality from SQL in Power BI logic)

[Anonymous]

Hi,

I am confused about how to achieve the values from previous row in Power BI (which means the lag functionality in SQL)

I have a table where the cumulative_inventory is already calculated based on the date,plant,material_id. Now I want a column which gives me the value from previous row of cumulative_Inventory based on the dates using a measure (Most preferable) or a calculated column. In other words for better understanding I would say that it is nothing but Yesterdays cumulative is Today's beginning_inventory.

I've tried using this measure but I am not achieving this completely. I tried modifying in different ways but not getting the appropriate results. 

Beginning_Inv =

CALCULATE (

MAXX ( Short_Ship, [Beg_Inv_Cum] ),

FILTER (

ALLSELECTED ( Short_Ship ),

Short_Ship[Planned_Dates] < MAX ( Short_Ship[Planned_Dates] )))

Below is the table for reference. Hope you understood the requirement.

 

Plant	Material_ID	Planned_Dates	Beg_Inv	Rec/Req_Quantities	Cumulative_Inv	Beginning_Inv
1110	402589	27-12-2021 00:00	9672	-720	8712
1110	402589	27-12-2021 00:00	9672	-240	8712
1110	402589	29-12-2021 00:00	9672	-240	8184	8712
1110	402589	29-12-2021 00:00	9672	-288	8184	8712
1110	402589	31-12-2021 00:00	9672	-288	7896	8184
1110	402589	10-07-2022 00:00	9672	-312	7584	8184
1110	402589	13-07-2022 00:00	19344	-1920	12840	7896
1110	402589	13-07-2022 00:00	19344	-1920	12840	7584
1110	402589	20-07-2022 00:00	19344	-624	3648	12840
						12840

The cumulative here is achieved by taking the running total of beg_inv and Rec/req_quantities i.e. 9672-720-240 = 8712 as both the rec/req_quantities are in the same date the cumulative is calculated by taking summation of both the values.

Now I would like to achieve the Beginning_inv column as mentioned in the table.

Hope someone gets the logic for this.

Thanks in advance

@amitchandak  @ChandeepChhabra @Ashish_Mathur @tamerj1 @Jihwan_Kim @Vahid-DM 

[Henrykong_ · Accepted solution]

Hi @Anonymous ,

You can try formula like below:

M_Pre = 
VAR pre_ =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] <= MAX ( 'Table'[Date] ) - 1 )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Volumn] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] = pre_ )
    )

If converted into a calculated column formula, you can use the top function to get the value of any of the first few lines (or summation). Specific reference to the following formula:

Column =
VAR pre_row =
    TOPN (
        1,
        FILTER (
            'Table',
            'Table'[Month] < EARLIER ( 'Table'[Month] )
                && 'Table'[Employee] = EARLIER ( 'Table'[Employee] )
        ),
        [Month], DESC
    )
VAR pre_val =
    MINX ( pre_row, [Sales] )
RETURN
    'Table'[Sales] + pre_val

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

To your visual, you should be dragging the Date column from the Calendar Table and remove the Planned_Dates column.  Ensure there is a relationship (Many to One and Single) from the Planned_Dates column and Date column of the Calendar Table.  Assuming Beg_Inv and Rec/Req_Quantities are measures, write this measure for computing Beginning_Inv

=[Beg_Inv]-calculate([Rec/Req_Quantities],datesbetween(calendar[date],minx(all(calendar[date]),calendar[date]),max(calendar[date])))

[tamerj1]

Hi @Anonymous 
This is the solution assuming calculated columns https://www.dropbox.com/t/0zUQp0PAocSjazNy 

Beginning_Inv = 
VAR CurrentPlant_MaterialTable = 
    CALCULATETABLE ( Short_Ship, ALLEXCEPT ( Short_Ship,Short_Ship[Plant], Short_Ship[Material_ID] ) )
VAR CurrentDate = 
    Short_Ship[Planned_Dates]
VAR PreviousTable =
    FILTER ( CurrentPlant_MaterialTable, Short_Ship[Planned_Dates] < CurrentDate )
VAR PreviousDate = 
    MAXX ( PreviousTable, Short_Ship[Planned_Dates] )
VAR PreviousRecord =
    FILTER ( PreviousTable, Short_Ship[Planned_Dates] = PreviousDate )
RETURN
    MAXX ( PreviousRecord, Short_Ship[Cumulative_Inv] )

[tamerj1]

Hi @Anonymous 
Is [Cumulative_Inv] a measure or a calculated column? Are trying to create a measure or a calculated column?

[Anonymous]

I am trying to create a measure . Cumulative_Inv is also a measure.

[tamerj1]

@Anonymous 
Here is the updated sample file https://www.dropbox.com/t/sksZWPPXQ5uJlEMq

Beginning_Inv = 
VAR CurrentDate = MAX ( Short_Ship[Planned_Dates] )
VAR PlantMaterialTable = 
    CALCULATETABLE (
        Short_Ship, 
        ALLEXCEPT ( Short_Ship,Short_Ship[Plant], Short_Ship[Material_ID] )
    )
VAR FilteredTable =
    FILTER ( PlantMaterialTable, Short_Ship[Planned_Dates] < CurrentDate )
VAR PreviousDate =
    MAXX ( FilteredTable, Short_Ship[Planned_Dates] )
RETURN
    CALCULATE ( 
        [Cumulative_Inv], 
        Short_Ship[Planned_Dates] = PreviousDate,
        PlantMaterialTable
    )

[tamerj1]

@Anonymous 

Ok, I will modify the code once I reach home.