topic Re: Need to find null value for a column on basis of previous value and factor in DAX Commands and Tips

Need to find null value for a column on basis of previous value and factor

snaraya — Mon, 27 Feb 2023 09:37:59 GMT

Hello everyone ,

i have been working on below data for 3days and couldnt find a correct solution

My sample data is as below

BU	Year	Projection	Factor	Index	Output
A	2019	53.27		0	53.27
A	2020	51.13		1	51.13
A	2021	57.83		2	57.83
A	2022	59.4		3	59.4
A	2023		-0.027	4	57.7962
A	2024		0.036	5	61.5384
A	2025		0.036	6	61.5384
A	2026		0.036	7	61.5384
A	2027		0.036	8	61.5384
A	2028		0.036	9	61.5384
A	2029		0.036	10	61.5384
A	2030		0.036	11	61.5384
B	2019	172.63		12	172.63
B	2020	168.54		13	168.54
B	2021	165.2		14	165.2
B	2022	151.13		15	151.13
B	2023		0.062313	16	183.3871
B	2024		0.063477	17	183.588
B	2025		0.045389	18	180.4655
B	2026		0.03	19	177.8089
B	2027		0.03	20	177.8089
B	2028		0.03	21	177.8089
B	2029		0.03	22	177.8089
B	2030		0.03	23	177.8089

to find the out column Output i used below DAX
Output =
VAR CurrentRow = 'Table'[Projection]
VAR PrevRow = CALCULATE( LASTNONBLANK('Table'[Projection],0 ), FILTER( 'Table', 'Table'[Index] < EARLIER('Table'[Index]) && NOT(ISBLANK('Table'[Projection])) ) )
VAR Factor = 'Table'[Factor] RETURN IF( ISBLANK(CurrentRow), PrevRow * (1 + Factor), CurrentRow )

the logic which i need is

for 2023- Projection of 2022*(1+Factor)

for 2024- Projection of 2023(calculated in previous step) *(1+Factor)

for 2025- Projection of 2024(calculated in previous step) *(1+Factor) and so on until 2030 for each BU

but my formula is using last non blank value of 2022 for calculating everything,so my 2023 value for both BU is correct from 2024 the value is wrong as it should use 2023 value to caculate 2024 and 2024 value for 2025 and so on

from 2019-2022 the data should be same

any help is appreciated

Re: Need to find null value for a column on basis of previous value and factor

BeaBF — Mon, 27 Feb 2023 15:50:32 GMT

snaraya Hi!

Output =
VAR CurrentRow = 'Table'[Projection]
VAR PrevRow = CALCULATE(
LASTNONBLANK('Table'[Output], 0),
FILTER('Table', 'Table'[Index] < EARLIER('Table'[Index]) && NOT(ISBLANK('Table'[Projection])))
)
VAR Factor = 'Table'[Factor]
RETURN IF(
NOT(ISBLANK(CurrentRow)), CurrentRow * (1 + Factor),
IF(
NOT(ISBLANK(PrevRow)), PrevRow * (1 + Factor),
BLANK()
)
)

BBF

Re: Need to find null value for a column on basis of previous value and factor

snaraya — Tue, 28 Feb 2023 03:57:44 GMT

I think i confused you ,

the output column is what i am getting using the formula mentioned in the post.

using your formula i am still getting same values which i am getting in output column of table..

values in my output column should be as follows

for 2023- Projection of 2022*(1+Factor of 2023)

for 2024- Projection of 2023(calculated in previous step) *(1+Factor of 2024)

for 2025- Projection of 2024(calculated in previous step) *(1+Factor of 2025) and so on until 2030 for each BU

Re: Need to find null value for a column on basis of previous value and factor

wdx223_Daniel — Tue, 28 Feb 2023 04:15:51 GMT

Output = VAR _tbl = TOPN ( 1, FILTER ( 'Table', 'Table'[BU] = EARLIER ( 'Table'[BU] ) && NOT ( ISBLANK ( 'Table'[Projection] ) ) ), 'Table'[Year] ) VAR _FirstYear = MAXX ( _tbl, 'Table'[Year] ) VAR _BaseProjection = MAXX ( _tbl, 'Table'[Projection] ) RETURN IF ( ISBLANK ( 'Table'[Projection] ), _BaseProjection * PRODUCTX ( FILTER ( 'Table', 'Table'[BU] = EARLIER ( 'Table'[BU] ) && 'Table'[Year] > _FirstYear && 'Table'[Year] <= EARLIER ( 'Table'[Year] ) ), 1 + 'Table'[Factor] ), 'Table'[Projection] )

Re: Need to find null value for a column on basis of previous value and factor

snaraya — Tue, 28 Feb 2023 04:45:10 GMT

Thankyou so much for this. Perfect

Re: Need to find null value for a column on basis of previous value and factor

wdx223_Daniel — Tue, 28 Feb 2023 04:52:25 GMT

Output = VAR _tbl = TOPN ( 1, FILTER ( 'Table', 'Table'[BU] = EARLIER ( 'Table'[BU] ) && NOT ( ISBLANK ( 'Table'[Projection] ) ) &&'Table'[Index]<EARLIER( 'Table'[Index] ) ), 'Table'[Year] ) VAR _FirstYear = MAXX ( _tbl, 'Table'[Year] ) VAR _BaseProjection = MAXX ( _tbl, 'Table'[Projection] ) RETURN IF ( ISBLANK ( 'Table'[Projection] ), _BaseProjection * PRODUCTX ( FILTER ( 'Table', 'Table'[BU] = EARLIER ( 'Table'[BU] ) && 'Table'[Year] > _FirstYear && 'Table'[Year] <= EARLIER ( 'Table'[Year] ) ), 1 + 'Table'[Factor] ), 'Table'[Projection] )

small revise, to consider the case that the years with projection value are not consecutive. such as the year 2026 is not blank, the old code will get a wrong value.

Re: Need to find null value for a column on basis of previous value and factor

snaraya — Fri, 24 Mar 2023 12:09:04 GMT

Thankyou for this output , things got little complicated as i need adtional column where we add two columns and subtract one other columns . sum reduction is a measure

BU	Year	projection	Output	factor	sum Reduction	efficiency	Reduction
A	2019	53.27	53.27131			0	0
A	2020	51.13	51.13091			0	0
A	2021	57.82	57.82309			0	0
A	2022	55.41	55.41486			0	-4.3
A	2023		53.91866	-0.027	0.116	0	-1.7
A	2024		55.85973	0.036	-0.216	0	-16
A	2025		57.87068	0.036	-0.792	0	7
A	2026		59.95402	0.036	-0.54	0	-2.6
A	2027		62.11237	0.036	-0.634	0	-2
A	2028		64.34841	0.036	-0.706	0	-5
A	2029		66.66496	0.036	-0.886	0	-10
A	2030		69.0649	0.036	-1.246	0	-2
B	2019	172.63	172.629			0	0
B	2020	168.54	168.5371			0	0
B	2021	165.2	165.1975			-0.326	-0.81
B	2022	151.13	151.1319			-9.513	-18
B	2023		160.5494	0.0623	-1.122	-5.22193	-5.64
B	2024		170.7406	0.0635	-1.5	-6.888	-1.62
B	2025		178.4903	0.0454	-1.146	-11.5	-23
B	2026		183.845	0.03	-1.448	-4	-10.25
B	2027		189.3604	0.03	-1.755	-3	-8
B	2028		195.0412	0.03	-1.995	0	-7.5
B	2029		200.8924	0.03	-2.22	-0.33	0
B	2030		206.9192	0.03	-2.22	0	0

want to adjust Output column as below, currently my formula has calculation until 1+factor.

Projection 2023= (Projection of 2022*(1+Factor)+effciency2023 +reduction2023-sum reduction2023)

Projection 2024-=(Projection of 2023(calculated in previous step) *(1+Factor) +effciency2024+reduction2024-sum reduction2024)

Projection 2025= (Projection of 2024(calculated in previous step) *(1+Factor) +effciency2025+reduction2025-sum reduction2025)and so on until 2030 for each BU

from 2019-2022 the data should be same

I tried addding efficiency, reduction & subtarcting sum reduction after the 1+factor bracket but it is just adding directly row to row and not at all subtracting may be because sum reduction is a measure

can you help me in adjusting this fromula in any way? wasted too many days in this.

Re: Need to find null value for a column on basis of previous value and factor

snaraya — Mon, 27 Mar 2023 05:52:41 GMT

Hi wdx223_Daniel is there a possibility of this working out or its not possible in dax? can you suggest ?

Re: Need to find null value for a column on basis of previous value and factor

wdx223_Daniel — Mon, 27 Mar 2023 07:47:38 GMT

is the result correct?

Output = VAR _tbl = TOPN ( 1, FILTER ( 'Table', 'Table'[BU] = EARLIER ( 'Table'[BU] ) && NOT ( ISBLANK ( 'Table'[Projection] ) ) && 'Table'[Year] < EARLIER ( 'Table'[Year] ) ), 'Table'[Year] ) VAR _FirstYear = MAXX ( _tbl, 'Table'[Year] ) VAR _BaseProjection = MAXX ( _tbl, 'Table'[Projection] ) VAR _tbl2 = FILTER ( 'Table', 'Table'[BU] = EARLIER ( 'Table'[BU] ) && 'Table'[Year] > _FirstYear && 'Table'[Year] <= EARLIER ( 'Table'[Year] ) ) VAR _tbl3 = ADDCOLUMNS ( _tbl2, "@f", 1 + 'Table'[factor], "@adj", VAR _f = PRODUCTX ( WINDOW ( 1, REL, -1, abs, SUMMARIZE ( _tbl2, 'Table'[BU], 'Table'[Year], 'Table'[factor] ), ORDERBY ( 'Table'[Year] ) ), 1 + 'Table'[factor] ) RETURN ( 'Table'[efficiency] + 'Table'[ Reduction] - 'Table'[sum Reduction] ) * IF ( _f, _f, 1 ) ) RETURN IF ( ISBLANK ( 'Table'[projection] ), _BaseProjection * PRODUCTX ( _tbl3, [@f] ) + SUMX ( _tbl3, [@adj] ), 'Table'[projection] )

Re: Need to find null value for a column on basis of previous value and factor

snaraya — Mon, 27 Mar 2023 08:12:52 GMT

yes the output is exactly correct..but my sum reduction is measure which i calculated and idk why its not subtracting it at all ...

Re: Need to find null value for a column on basis of previous value and factor

wdx223_Daniel — Mon, 27 Mar 2023 08:29:33 GMT

maybe the measure can not get the result in the row context of _tbl2

Re: Need to find null value for a column on basis of previous value and factor

snaraya — Tue, 28 Mar 2023 05:34:17 GMT

Thankyou so much for this formula, i converted my Measure into a column and now everything works fine.