Previous_Value_as per Day_range

Hii MHTANK 

You can achieve this in Power Query (M Language) by following these steps:

Solution Approach

1. Group the data by Year, Month, and Day_Range, and calculate the average (Avg).
2. Sort the grouped data by Year, Month, and Day_Range to ensure correct ordering.
3. Add a column for the previous period's average (Prev) by referencing the previous row’s Avg value.

Step-by-Step Power Query Solution

1. Load your data into Power Query.
2. Transform the data using the following M code:

let
    // Load the data
    Source = YourTable,  
    
    // Group by Year, Month, and Day_Range and calculate the average value
    GroupedData = Table.Group(Source, {"Year", "Month", "Day_Range"}, 
        {{"Avg", each List.Average([Value]), type number}}),
    
    // Sort the table by Year, Month, and Day_Range
    SortedData = Table.Sort(GroupedData, {{"Year", Order.Ascending}, {"Month", Order.Ascending}, {"Day_Range", Order.Ascending}}),

    // Add Previous column using Index and referencing the previous row
    AddPrevColumn = Table.AddColumn(SortedData, "Prev", 
        each try SortedData[Avg]{List.PositionOf(SortedData[Avg], _)-1} otherwise null, 
        type nullable number
    )
    
in
    AddPrevColumn

Explanation of Code

1. Groups the data by Year, Month, and Day_Range and calculates the Avg (average of Value column).
2. Sorts the table to ensure correct chronological order.
3. Adds the Prev column by:
   • Using List.PositionOf to get the previous row's Avg value.
   • Using try...otherwise null to prevent errors in the first row where there's no previous value.

Expected Output

Year Month Day_Range Avg Prev

If this helps, I would appreciate your KUDOS!
Did I answer your question? Mark my post as a solution!