Calculate Recursively (?)

[konselz]

Hi everyone,

I am kind of stuck with calculating values recursively.

See the attached pic.

Assume we only have / are provided with the first record (1 Jan 2023). 

The rule is ColC = ColA - ColB.

For Feb 23 onwards, the rule is: ColA is the previous value of ColC 

From my understanding this requires DAX measure to do recursive calculation. Is that correct?

Are there any other ways to achieve this without recursion as DAX does not really support recursion, e.g. using EARLIER etc?

I had a look at this video https://www.youtube.com/watch?v=Kj4BPm-Kwvw which using OFFSET and WINDOW functions, but I can't get it to work 😞

Any guidance and help will be much appreaciated.

Thanks

[barritown · Accepted solution]

Hi @konselz,

With the new rules it's better to solve task in Power Query I guess. If you want a clean solution, try asking gurus in the Power Query section of this forum.

The not-very-clean amateurish solution from me may look like this:

1) First, you go to Power Query and create an index column based on the Date column.

2) Then you create a function like this:

= ( i as number, a as number, b as number ) =>
let 
    f = List.Generate(
        () => [j = 1, x = a, y = a - b],
        each [j] < i,
        each [y = [y] - 0.5*[x],
        x = [y],
        j = [j] + 1 ],
        each [y] )
in
    if (i = 1) then a else List.Last(f)

3) Then you add a custom column like this:

= MyFunction ( [Index], List.Max(#"Changed Type"[ColA]), List.Max(#"Changed Type"[ColB]) )

You will have ColA after that (ColB and ColC can be generated either via Power Query or via DAX):

Check the file here - https://www.dropbox.com/scl/fi/cjue59hlw4jrm8olxtoh9/community-no-recursion-2.pbix?rlkey=lt0bdlbolq9...

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[barritown]

Hi @konselz,

I think you can do without recursion or window functions here. 

2 calculated columns should do it for you.

In plain text:

ColC = 
VAR CurDate = [Date]
RETURN SUM ( Data[ColA] ) - SUMX ( FILTER ( Data, [Date] <= CurDate ), [ColB] )

ColA_filled = 
VAR CurDate = [Date]
VAR PrevDate = MAXX ( FILTER ( Data, Data[Date] < CurDate ), [Date] )
RETURN COALESCE ( [ColA], MINX ( FILTER ( Data, Data[Date] = PrevDate ), [ColC] ) )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[konselz]

Thank you Alexander. I think I can follow that.

However, to complicate things more, actually ColB values are not provided, but needs to be calculated based on ColA from previous month * a given factor value.

See the picture attached.

So the rules are:

1. Jan 2023 record (ColA, ColB, ColC) values are provided

2. Factor value of 0.5 is provided

3. From 1 Feb 2023 onwards,

ColA = ColC from previous month

ColB = ColA from previous month * Factor value

ColC = ColA - ColC

Is this possible using the approach you mentioned above, or need a different way of solving?

Thanks again