Forum Discussion

nirvana_moksh's avatar
nirvana_moksh
Impactful Individual
7 years ago

Fill with Previous Value

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 ...
Need Help
Show and Tell
Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

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.