Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

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.
 
PlantMaterial_IDPlanned_DatesBeg_InvRec/Req_QuantitiesCumulative_InvBeginning_Inv
111040258927-12-2021 00:009672-7208712 
111040258927-12-2021 00:009672-2408712 
111040258929-12-2021 00:009672-24081848712
111040258929-12-2021 00:009672-28881848712
111040258931-12-2021 00:009672-28878968184
111040258910-07-2022 00:009672-31275848184
111040258913-07-2022 00:0019344-1920128407896
111040258913-07-2022 00:0019344-1920128407584
111040258920-07-2022 00:0019344-624364812840
      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 

Need Help
Tips and Tricks
  • Henrykong_'s avatar
    Henrykong_
    4 years ago

    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.

7 Replies

  • Henrykong_'s avatar
    Henrykong_
    Icon for Resolver I rankResolver I
    4 years ago

    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.

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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

       

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Anonymous 

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

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

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

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

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