Fill with Previous Value

[nirvana_moksh]

Hello All,

Below is the sample data that I have at hand. The data in large consists of Head --> Branch --> Branch Section which can be seen as the hierarchy even. What I am trying to achieve for the data after sorting it by the said hierarchy is to fill the blanks by the previous quarter value. I was kinda successful by first sorting the data in Power Query, replacing all blanks by null and then filling down both Amount 1 and Amount 2 column which worked for most but not for all. Also, this would work not as intended if Q1 itself is blank and is being populated by value of the above group:

HEAD	BRANCH	BRANCH SECTION	AMOUNT 1	AMOUNT 2	QUARTER YEAR
A1	BRANCH 1	T1		2456	Q1 2017
A2	BRANCH 1	T1			Q2 2017
A2	BRANCH 1	T2	1500	3100	Q3 2017
A2	BRANCH 1	T2	4200		Q4 2017
A1	BRANCH 1	T3		2400	Q1 2018
A1	BRANCH 1	T3		25402	Q2 2018
A1	BRANCH 1	T4	1000	8172	Q3 2018
A2	BRANCH 1	T4	3200		Q4 2018
A1	BRANCH 2	T1			Q1 2017
A2	BRANCH 2	T2	1500	1200	Q2 2017
A1	BRANCH 2	T3	1899	500	Q3 2017
A1	BRANCH 2	T4			Q4 2017

How the data should look like (the ones in RED are post the solution in place):

HEAD	BRANCH	BRANCH SECTION	AMOUNT 1	AMOUNT 2	QUARTER YEAR
A1	BRANCH 1	T1		2456	Q1 2017
A2	BRANCH 1	T1		2456	Q2 2017
A2	BRANCH 1	T2	1500	3100	Q3 2017
A2	BRANCH 1	T2	4200	3100	Q4 2017
A1	BRANCH 1	T3	4200	2400	Q1 2018
A1	BRANCH 1	T3	4200	25402	Q2 2018
A1	BRANCH 1	T4	1000	8172	Q3 2018
A2	BRANCH 1	T4	3200	8172	Q4 2018
A1	BRANCH 2	T1			Q1 2017
A2	BRANCH 2	T2	1500	1200	Q2 2017
A1	BRANCH 2	T3	1899	500	Q3 2017
A1	BRANCH 2	T4	1899	500	Q4 2017

I tried using Calculate(FirstNonBlank() as well but without success.

Thank You

[Ashish_Mathur]

Hi,

Here's one way to do it with DAX.  I created the following 3 calculated column formulas

Date = DATE(RIGHT(Data[QUARTER YEAR],4),IF(LEFT(Data[QUARTER YEAR],2)="Q1",3,IF(LEFT(Data[QUARTER YEAR],2)="Q2",6,IF(LEFT(Data[QUARTER YEAR],2)="Q3",9,12))),1)

Revised amount1 = if(ISBLANK([AMOUNT 1]),LOOKUPVALUE([AMOUNT 1],Data[Date],CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Date]<EARLIER(Data[Date])&&Data[BRANCH]=EARLIER(Data[BRANCH])&&[AMOUNT 1]>0)),Data[BRANCH],Data[BRANCH]),[AMOUNT 1])

Revised amount2 = if(ISBLANK([AMOUNT 2]),LOOKUPVALUE([AMOUNT 2],Data[Date],CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Date]<EARLIER(Data[Date])&&Data[BRANCH]=EARLIER(Data[BRANCH])&&[AMOUNT 2]>0)),Data[BRANCH],Data[BRANCH]),[AMOUNT 2])

Hope this helps.

[v-danhe-msft]

Hi @nirvana_moksh,

Based on my test, you could refer to below steps in query editor:

1.Copy [AMOUNT 1] and fill down the copy columns:

2.Create a custom column with below code:

if [AMOUNT 1]=null and [BRANCH SECTION]="T1" then null else [#"AMOUNT 1 - Copy"]

You could also download the pbix file to have a view.

Regards,

Daniel He

[nirvana_moksh]

@v-danhe-msft - But this won't be optimal in regards to when I integrate this to the whole data set, as that includes 1k+ branches and multiple Branch Sections as well. Also, wont filling from previous value cause wrong filling of data, for instance if last row of Branch 1 has a value and then Branch 2 starts fresh it will inherit the value of Branch 1.

[Zubair_Muhammad]

@nirvana_moksh

This is one way in Power Query.

Basically we have to do a "GroupBy" branch step first and then apply the 'FillDown" function

Please see attached file as well for the steps

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZDNCoMwEIRfRXL2sDvZ1Hi0XjwVLL2J7/8a3Y1o2/WnEDIKH8OXmabQcajD/dk9+qGyz5ddeiDppjFyBeImzLWiOEYNwwVm/5yINCKXGOMfXFA4Q+UL9a5xdV1ai2u+RpMQNuETVkyYSmfmBptwPhY2PDrhfTPcYifDwi3GS/HPwL7V3sa5bTXSfl9Py8dh3XZ+Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [HEAD = _t, BRANCH = _t, #"BRANCH SECTION" = _t, #"AMOUNT 1" = _t, #"AMOUNT 2" = _t, #"QUARTER YEAR" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"HEAD", type text}, {"BRANCH", type text}, {"BRANCH SECTION", type text}, {"AMOUNT 1", Int64.Type}, {"AMOUNT 2", Int64.Type}, {"QUARTER YEAR", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"BRANCH"}, {{"All_Rows", each Table.FillDown(_,{"AMOUNT 1","AMOUNT 2"}), type table}}),
    #"Expanded All_Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All_Rows", {"HEAD", "BRANCH SECTION", "AMOUNT 1", "AMOUNT 2", "QUARTER YEAR"}, {"HEAD", "BRANCH SECTION", "AMOUNT 1", "AMOUNT 2", "QUARTER YEAR"})
in
    #"Expanded All_Rows"

[Greg_Deckler]

See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

[nirvana_moksh]

Thanks @Greg_Deckler, going over your post now.