PowerBI Add calculated column that reads previous row's value

Question

I have the following Data:

And my attempt at a formula to determine the anticipate demand for my receipts every month based on receipt issuance of the month prior:

= Table.AddColumn(#"Added Custom3", "Prelim Anticipated Demand (All Years)", each if [ReceiptsIssuedLastYear] = null then 
   [#"AvgYearlyReceiptsIssued (All Years)"]+([#"AvgYearlyReceiptsIssued (All Years)"]*[#"AvgPercentageOfReceiptsIssuedThisMonth (All Years)"])
else 
    [#"AvgYearlyReceiptsIssued (All Years)"]+(([#"AvgYearlyReceiptsIssued (All Years)"]-[ReceiptsIssuedLastYear]))*[#"AvgPercentageOfReceiptsIssuedThisMonth (All Years)"])

I recognize that my formula is incomplete and very wrong, and that's why I'm not getting the result I expect. This is difficult to describe but here goes:

The idea is to identify and forecast demand for receipts every month of each year based on the average monthly issuance of receipts (AvgPercentageOfReceiptsIssuedThisMonth) and whether or not the demand for the previous month was met.

In this case, 2013 is the earliest month in the database. Therefore, the ReceiptsIssuedLastYear is null. Therefore, the demand for this first year should equate to the average number of receipts issued per year (AvgYearlyReceiptsIssued) and since were are looking at a monthly basis, multiply that value by the average monthly issuance of receipts (AvgPercentageOfReceiptsIssuedThisMonth)..

This explains this part of the formula:

 if [ReceiptsIssuedLastYear] = null then 
   [#"AvgYearlyReceiptsIssued (All Years)"]+([#"AvgYearlyReceiptsIssued (All Years)"]*[#"AvgPercentageOfReceiptsIssuedThisMonth (All Years)"])

However, that code applies to all months in 2013, when I want it to only apply to the earliest year and month in the database.

The reason is because from there, for each month going forward, the idea is to determine the anticipated demand of this month by using the "Anticipated Demand last month" subtract from it the "Receipts issued this month" (Receipts Issued) and add the result to the "anticipated demand of this month' and repeat that process each and every single month going forward.

The anticipated result should follow this (numbers rounded up, percentage at 2 decimals):

Start of Month	Anticipated Demand Result	Formula (Columns)	Formula (Numbers)
2013-01-01	339478	=AvgYearlyReceiptsIssued (All Years)*AvgPercentageOfReceiptsIssuedThisMonth (All Years)	3334753*0.1018
2013-02-01	52319	=(AvgYearlyReceiptsIssued (All Years)*AvgPercentageOfReceiptsIssuedThisMonth (All Years))+(Anticipated Demand of Previous Row - ReceiptsIssued Previous Row)	(3334753*0.0611)+(339478-490700)
2013-03-01	-105376	=(AvgYearlyReceiptsIssued (All Years)*AvgPercentageOfReceiptsIssuedThisMonth (All Years))+(Anticipated Demand of Previous Row - ReceiptsIssued Previous Row)	(3334753*0.0410)+(52319-294420)
2013-04-01	-163264	=(AvgYearlyReceiptsIssued (All Years)*AvgPercentageOfReceiptsIssuedThisMonth (All Years))+(Anticipated Demand of Previous Row - ReceiptsIssued Previous Row)	(3334753*0.0415)+(-105376-196280)
2013-05-01	-221152	=(AvgYearlyReceiptsIssued (All Years)*AvgPercentageOfReceiptsIssuedThisMonth (All Years))+(Anticipated Demand of Previous Row - ReceiptsIssued Previous Row)	(3334753*0.0731)+(-163264-196280)
2013-06-01	-55425	=(AvgYearlyReceiptsIssued (All Years)*AvgPercentageOfReceiptsIssuedThisMonth (All Years))+(Anticipated Demand of Previous Row - ReceiptsIssued Previous Row)	(3334753*0.1527)+(-221152-343490)
2013-07-01	-246910	=(AvgYearlyReceiptsIssued (All Years)*AvgPercentageOfReceiptsIssuedThisMonth (All Years))+(Anticipated Demand of Previous Row - ReceiptsIssued Previous Row)	(3334753*0.1633)+(-246910-736050)

I hope my intent is clear. I will do my best to address any questions coming out of this. I do recognize that the solution is likely only possible in DAX, which is fine but I honestly have no idea where to start on getting this right.

EDIT:

I am adding the following to offer some more clarification to my data and expected results. Assume that column G is the column we are attempting to calculate:

Row	Calculation	Expected Result
1	D1*E1	339478
2	(D2*E2)+(G1-F2)	52319
3	(D3*E3)+(G2-F3)	-105376
4	(D4*E4)+(G3-F4)	-163264
5	(D5*E5)+(G4-F5)	-221152
6	(D6*E6)+(G5-F6)	-55425
7	(D7*E7)+(G6-F7)	-246910

Mik · Accepted Answer · 2022-06-30 10:06:35Z

2

The idea is to cut the table up to datevalue then sum All multiplications and deduct sum all receiptsIssuedPreviousRow that we have in the var table. Some simple math in addition. G1= (D1*E1)+0-0; G2=D2*E2+G1-F2 and it is the same as G2= D2*E2+(D1*E1)-F2; Then G3= D3*E3+(D2*E2+D1*E1-F2)-F3. lets group values G3=(D3*E3+D2*E2+D1*E1)-(F2+F3). So, G3 is a sum of all D*E up to current row where we deduct the sum of F up to current row. This logic is in the measure.

VAR DayInRow = CALCULATE(MAX([Start of Month]))
VAR tmpTbl =
    FILTER(
           'TableName'
           ,'TableName'[Start of Month]<=DayInRow 
    )
                        
VAR finalTable = 
        ADDCOLUMNS(
            tmpTbl
            ,"receiptsIssuedPreviousRow"
                    ,VAR previouStartOfMonth = 
                        DATEADD('TableName'[Start of Month],-1, Month)
                    RETURN
                                CALCULATE(
                                        SUM('TableName'[Receipts issued])
                                        ,ALL('TableName') -- to excude the row context
                                        ,previouStartOfMonth 
                                )
            ,"multiplication"
                    ,[AvgYearlyReceiptsIssued (All Years)]*[AvgPercentageOfReceiptsIssuedThisMonth (All Years)]
               )
VAR result = SUMX(finalTable,[multiplication]- [receiptsIssuedPreviousRow])   
RETURN
    ROUND(result,2)

edited Jun 30, 2022 at 10:06

answered Jun 28, 2022 at 9:10

Mik

2,1011 gold badge7 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

18 Comments

Sweepster Over a year ago

Thank you for pointing out the "anticipated Demand of previous row". I forgot to change that value in my calculations. They are corrected in the question now. At Issue is that the column 'TableName'[Anticipated Demand] IS the column that need to be calculated. It doesn't exist in the original data table. We need to take the value of the calculated anticipated demand in the first row to calculate the value of the second row, then use the value of the second row to calculate the third and so on.

Mik Over a year ago

I changed the code. Please let me know if its works

Sweepster Over a year ago

Unfortunately, it returns 5490719.99999994 in all rows

Mik Over a year ago

Ok, I'll test the code

Mik Over a year ago

now it's works. Test it ). I changed CALCULATETABLE to FILTER and removed ALL(). Everything else remains the same.

|

Collectives™ on Stack Overflow

PowerBI Add calculated column that reads previous row's value

1 Answer 1

18 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

18 Comments

Your Answer

Sign up or log in

Post as a guest

Related