Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  How to reference within a calculated column?

    Posted Jun 29, 2022 12:00 AM
    Edited by Tom Paulson Jun 29, 2022 12:01 AM
    I have the following Data:

    The idea is to identify and forecast demand for receipts every month of each year. This would be column G. Obviously, we don't know what demand will be for month (row) 1. However, we do know what the number of average yearly receipts issued is and the avergae receipts issued for each month is so we can use those figures as a starting point by taking take the AvgYearlyReceiptsIssued (All Years) and multiply it by the AvgPercentageOfReceiptsIssuedThisMonth (All Years).

    For each month that follows, demand is to be determined by subtracting the expected demand of the previous month and subtracting the number of receipts issued in that previous month and adding the result to our average monthly issued receipts baseline. This process repeats for every month until the end of the table.

    The below is a breakdown of the expected results for column G:
    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

    At issue, is the fact that the calculation requires column G to self-reference and iterate. My guess is that this requires using some sort of temporary table that exists inside of a loop but I have no idea how to go about this, let alone achieve the result.

    Any ideas?


    ------------------------------
    Tom Paulson
    ------------------------------


  • 2.  RE: How to reference within a calculated column?

    Bronze Contributor
    Posted Jun 29, 2022 03:30 PM
      |   view attached
    Hi Paul,

    You can't and you don't need to self-reference.
    Formula are calculated in a context, we need to define the proper context.
    Your formula
    (D2*E2)+(G1-F2) can be transformed as:
    (D2*E2)+(D1*E1)-F2)
    (D3*E3)+(D2*E2)+(D1*E1)-F2-F3) and so on
    ==> iterator
    This code should work
    Result = 
    VAR CurrentDate = MAX( 'Table'[Start of Month] )
    VAR PreviousRow = CALCULATE( MAX( 'Table'[Start of Month] ), 'Table'[Start of Month] < CurrentDate )
    VAR PreviousReceiptsIssued =
    	CALCULATE(
    		SUM( 'Table'[Receipts issued] ),
    		REMOVEFILTERS( 'Table'[Start of Month] ),
    		'Table'[Start of Month] < CurrentDate
    	)
    VAR G =
    	CALCULATE(
    		SUMX(
    			'Table',
    			'Table'[%AvgPercentageOfReceiptsissued This Month(All Years)]
    				* 'Table'[AvgYearlyReceiptsissued(All Years)]
    		),
    		REMOVEFILTERS( 'Table'[Start of Month] ),
    		'Table'[Start of Month] <= CurrentDate
    	)
    RETURN
    	G - PreviousReceiptsIssued​

    Here is the result:
    There is discrepancy with your expected result, but applying your formula in Excel gives the same result.
    If I missed anything, let me know.
    Checked by yourself in the pbix uploaded


    ITERATOR  ITERATOR 

    ------------------------------
    Marc Schroyen
    Liège Belgium
    ------------------------------

    Attachment(s)

    pbix
    Paulson.pbix   23 KB 1 version


  • 3.  RE: How to reference within a calculated column?

    Posted Jun 29, 2022 05:40 PM
    Amazing! The discrepancy is caused by my rounding the E1 value in my calculations whereas your solution doesn't. Thank you!

    ------------------------------
    Tom Paulson
    ------------------------------