CODEWITHSUNDEEP
powerbidax
Sweepster
Sweepster

Reputation: 1949

PowerBI Add calculated column that reads previous row's value

I have the following Data: enter image description here

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:

enter image description here

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

Upvotes: 1

Views: 1289

Answers (1)

Mik
Mik

Reputation: 2103

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)

Upvotes: 2

Related Questions