Value from one row as starting value in subsequent row

[mw123]

Hi everyone,

I need to figure out a way to carry over an ending amount for a period to be the beginning amount on a subsequent row (next period) and have it change dynamically based on the selection of values that are derived from many slicers. The data I am using are monthly extractions. Each extraction is broken out at the source data by about 10 columns, each row of data being unique.

Example:

                                       Types         TypeA            TypeB             

Start Date   End Date                       

4/18/2016   5/16/2016                            End#                End#            

5/16/2016   6/20/2016                Beg#    End#   Beg#    End#  

                                                   **^I want this Beg # to come from the end # in the previous row. Continue this for                                                         each row of values.

I have tried to implement this formula as a calculated column:  = CALCULATE(SUM('table'[data]), FILTER (table, 'table'[daterankcolumn]=EARLIER([daterankcolumn2]) && [Types] = EARLIER([Types])))

^The above formula works well in implementing a static value for the beg#; however, when I use slicers in an attempt to dynamically change the data, it does not work as intended.

Moreover, I have also tried the above formula as a measure in an attempt to add the ability to dynamically change the data. However, this also didn't work because the measure does not work with the earlier function.

I am thinking maybe there is a way to add a table and consequently combine the data with the goal of accomplishing what I am looking for. I am not sure the best approach to take in doing that. Or perhaps, there is another way that would be more suitable.

Please let me know if there's anything else I can further clarify. Any help would be great! 

[v-huizhn-msft]

Hi @mw123,

Please try the following formula to create a measure and check if works fine.

CALCULATE(SUM('table'[data]), FILTER (table, 'table'[daterankcolumn]=MAX([daterankcolumn2]) && [Types] = MAX([Types])))

If it still not resolve your issue, please share your sample table for further analysis. list your resouce table and expected result. You can create fake data if your data is private.

Best Regards,
Angelia

[mw123]

Hi Angelia, 

Thanks for getting back so quickly. Unfortunately, that formula did not work for me.

I attached a screenshot of a sample set of the source data I am working with in excel. I  selected the top 10 rows for each date to get a generalized picture of the format; however, in reality, there are about 100k records for each date. Each record is unique. The dates of the extracts are every 4 weeks except for a few times per year where they are every 5 weeks. 

In the Power BI report, I am already passing in several variables. Below is a list of the variable names with their corresponding formulas that directly correlate to this issue I am facing:

Beginning of Period = CALCULATE (
MAX ([End of Period] ), FILTER ( table, table[Ending of Period] < EARLIER (table[Ending of Period] ) ) )

DateRank2= Table.AddColumn(#"Added Index2", "Custom.1", each if [Date Rank] = "1" then "0" else if [Date Rank] = "2" then "1" else if [Date Rank] = "3" then "2" else if [Date Rank] = "4" then "3" else if [Date Rank] = "5" then "4" else if [Date Rank] = "6" then "5" else if [Date Rank] = "7" then "6" else if [Date Rank] = "8" then "7" else if [Date Rank] = "9" then "8" else if [Date Rank] = "10" then "9" else if [Date Rank] = "11" then "10" else if [Date Rank] = "12" then "11" else if [Date Rank] = "13" then "12" else if [Date Rank] = "14" then "13" else null )

DateRank= Table.AddColumn(#"Removed Columns1", "Custom.1", each if [End of Period] = #date(2016, 5, 16) then "1" else if [End of Period]= #date(2016, 6, 20) then "2" else if [End of Period] = #date(2016, 7, 18) then "3" else if [End of Period] = #date(2016, 8, 15) then "4" else if [End of Period] = #date(2016, 9, 19) then "5" else if [End of Period] = #date(2016, 10, 17) then "6" else if [End of Period] = #date(2016, 11, 14) then "7" else if [End of Period] = #date(2016, 12, 19) then "8" else if [End of Period] = #date(2017, 1, 16) then "9" else if [End of Period] = #date(2017, 2, 13) then "10" else if [End of Period] = #date(2017, 3, 20) then "11" else if [End of Period] = #date(2017, 4, 17) then "12" else if [End of Period] = #date(2017, 5, 15) then "13" else if [End of Period] = #date(2017, 6, 19) then "14" else null )

In the Power BI report, I am creating a matrix with the rows as the "Beginning of Period" variable I created and then the "End of Period", which comes from the source data. The column I am passing into the matrix is the "Type" field from the source data. The values I am passing into the report are a sum of the "Revenue" field which gets me my Ending Balance for the period for each type and then also the formula field (from the prior post) I created to get the Beginning Balance on the subsequent row. That is the part I need help on. I need that value to change dynamically based on the slicer selected. Right now, I am using the calculated formula, but I have to take the min of that amount because it takes a sum of the total for each date; hence, when I try to sum that amount, I get a massive number. Therefore, my minimum value is actually the amount I pass into the report. That number is correct; however, I cannot get that number to change dynamically based on the selections in the slicers (I'm assuming it is because it is the min amount). Let me know if you need any additional information. Thanks again for the help. 

Sample Data Set 

[v-huizhn-msft]

Hi @mw123,

Do you mind share your excel file or .pbix file for further analysis?

Best Regards,
Angelia

[mw123 · Accepted solution]

Hi Angelia,

I actually no longer need this-I was able to come up with a solution by creating a date table and linking the date from my source table to the date in the date table. Thank you though for the help!