- Power BI forums
- Updates
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Power Platform Integration - Better Together!
- Power Platform Integrations (Read-only)
- Power Platform and Dynamics 365 Integrations (Read-only)
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

- Power BI forums
- Forums
- Get Help with Power BI
- DAX Commands and Tips
- calculation based on the previous value

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

calculation based on the previous value

11-04-2022
09:44 AM

Hi all,

I am stuck with creating a dax function, i really need help..

Here is an example, the final in the table should be my result, I have a value for 2019 which is the starting point of the calculation, then its about adding up the previous value calculated multiplied by the factor.

Thanks in advance for your support.

Solved! Go to Solution.

2 ACCEPTED SOLUTIONS

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-05-2022
12:40 AM

Hi @ybyb23

What do you mean by "DAX function"? In DAX there are no functions. Either measures or calculated columns/tables. The above can be done in DAX as a measure because even though the problem in nature is recursive (and DAX does not support such constructs, bar the special kind called "side recursion"), a formula can be crafted that'll be fully iterative. If you need a table with a calculated column, please use Power Query for this.

Here's how to do it in Power Query:

```
// T
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtFTSUTK0ABIGSrE6IBEjAyAHiPSMYAKGUAFjmIARVMAEJmAMFTCFCZhABcyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Kpi = _t, Factor = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Kpi", Int64.Type}, {"Factor", type number}}),
ReplicateKpiDown = Table.FillDown(#"Changed Type",{"Kpi"}),
AddIndex = Table.AddIndexColumn(ReplicateKpiDown, "Index", 0, 1, Int64.Type),
MoveIndexToFirstColumn = Table.ReorderColumns(AddIndex,{"Index", "Year", "Kpi", "Factor"}),
AddCofactor = Table.AddColumn(MoveIndexToFirstColumn, "Cofactor", each 1 - [Factor]),
MakeFinalCalculation = Table.AddColumn(AddCofactor, "Final", each [Kpi] * List.Product(List.FirstN(AddCofactor[Cofactor], [Index] + 1)))
in
MakeFinalCalculation
```

I'll do it in DAX as well and then paste it here. Bear with me...

Here's the outcome of the calculation in PQ and DAX:

The file where this is done has been attached...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-05-2022
05:10 PM

DAX certainly has functions (e.g. MAX, PRODUCTX, CALCULATETABLE). It's just that the user cannot define functions, except in limited sorts of ways.

Also note that while DAX cannot do recursion, the M language can. This means that instead of an O(N^2) solution that's required in DAX, we can get much better performance for large tables with an O(N) solution that uses recursion. Using List.Accumulate or List.Generate is a common way to implement recursive logic. For example,

```
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NcjLCQAgDATRXvYcJB8VrSWk/zY0h70MzMuEq10I7PwoSlpc//QPpxglKE6ZlKAsyqRsVD0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text)) in type table [Year = _t, Kpi = _t, Factor = _t]),
ChangeType = Table.TransformColumnTypes(Source, {{"Year", Int64.Type}, {"Kpi", Int64.Type}, {"Factor", type number}}),
NewColumn = List.Generate(
() => [x = ChangeType[Kpi]{0}, f = List.Buffer(ChangeType[Factor])],
each not List.IsEmpty([f]),
each [
x = [x] * (1 - List.First(f)),
f = List.RemoveFirstN([f], 1)
],
each [x]
),
AddColToTable = Table.FromColumns(Table.ToColumns(ChangeType) & {NewColumn}, Table.ColumnNames(ChangeType) & {"Final"})
in
AddColToTable
```

7 REPLIES 7

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-05-2022
12:40 AM

Hi @ybyb23

What do you mean by "DAX function"? In DAX there are no functions. Either measures or calculated columns/tables. The above can be done in DAX as a measure because even though the problem in nature is recursive (and DAX does not support such constructs, bar the special kind called "side recursion"), a formula can be crafted that'll be fully iterative. If you need a table with a calculated column, please use Power Query for this.

Here's how to do it in Power Query:

```
// T
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtFTSUTK0ABIGSrE6IBEjAyAHiPSMYAKGUAFjmIARVMAEJmAMFTCFCZhABcyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Kpi = _t, Factor = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Kpi", Int64.Type}, {"Factor", type number}}),
ReplicateKpiDown = Table.FillDown(#"Changed Type",{"Kpi"}),
AddIndex = Table.AddIndexColumn(ReplicateKpiDown, "Index", 0, 1, Int64.Type),
MoveIndexToFirstColumn = Table.ReorderColumns(AddIndex,{"Index", "Year", "Kpi", "Factor"}),
AddCofactor = Table.AddColumn(MoveIndexToFirstColumn, "Cofactor", each 1 - [Factor]),
MakeFinalCalculation = Table.AddColumn(AddCofactor, "Final", each [Kpi] * List.Product(List.FirstN(AddCofactor[Cofactor], [Index] + 1)))
in
MakeFinalCalculation
```

I'll do it in DAX as well and then paste it here. Bear with me...

Here's the outcome of the calculation in PQ and DAX:

The file where this is done has been attached...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-05-2022
05:10 PM

DAX certainly has functions (e.g. MAX, PRODUCTX, CALCULATETABLE). It's just that the user cannot define functions, except in limited sorts of ways.

Also note that while DAX cannot do recursion, the M language can. This means that instead of an O(N^2) solution that's required in DAX, we can get much better performance for large tables with an O(N) solution that uses recursion. Using List.Accumulate or List.Generate is a common way to implement recursive logic. For example,

```
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NcjLCQAgDATRXvYcJB8VrSWk/zY0h70MzMuEq10I7PwoSlpc//QPpxglKE6ZlKAsyqRsVD0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text)) in type table [Year = _t, Kpi = _t, Factor = _t]),
ChangeType = Table.TransformColumnTypes(Source, {{"Year", Int64.Type}, {"Kpi", Int64.Type}, {"Factor", type number}}),
NewColumn = List.Generate(
() => [x = ChangeType[Kpi]{0}, f = List.Buffer(ChangeType[Factor])],
each not List.IsEmpty([f]),
each [
x = [x] * (1 - List.First(f)),
f = List.RemoveFirstN([f], 1)
],
each [x]
),
AddColToTable = Table.FromColumns(Table.ToColumns(ChangeType) & {NewColumn}, Table.ColumnNames(ChangeType) & {"Final"})
in
AddColToTable
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-08-2022
07:46 AM

Thanks for the solution! It worked perfectly.

I can understand it is important to define precisly the issue, however a function can be an expression, or an operation, as far as I know this is what is about in Dax. I think it's a valid point to highlight it in this forum community by creating a power bi glossery.

I have finally opted to use Python to create the recursive logic.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-06-2022
01:23 AM

"*DAX certainly has functions (e.g. MAX, PRODUCTX, CALCULATETABLE). It's just that the user cannot define functions, except in limited sorts of ways.*"

@AlexisOlson, when I said "functions," I meant **user-defined functions**, obviously, because this is what @ybyb23 asked for. They do not exist in DAX, not even "in limited sort of ways." Measures can't be considered functions, either, in any way because they can't take arguments, at least directly. But if you want to abuse terminology... well, yes, you can name any object you want anything you want. Nobody can prevent you from this.

*"Also note that while DAX cannot do recursion, the M language can."*

@AlexisOlson, have you read what I wrote there above? I did write that even if in DAX there's only side-recursion allowed, **full recursion exists in M**.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-07-2022
08:43 AM

Saying "In DAX there are no functions" is not accurate, so I clarified this meant user-defined functions. As you said, measures cannot take arguments directly. However, you can pass filters to a measure that behave like arguments. This is limited by the fact that the domain of the function you're recreating must already be defined in the data model (you can't have arbitrary inputs; it must come from values in an existing table).

As a simple example of a limited function, let T be a table with a column [x]. Define the measure [x^2] as

```
VAR _x = SELECTEDVALUE ( T[x] )
RETURN _x * _x
```

We can now use this measure as a function so long as the input exists in column T[x]. For example,

```
10^2 = [x^2](T[x] = 10)
= CALCULATE ( [x^2], T[x] = 10 )
```

For Power Query, I'm not claiming you said anything wrong either. The M code you wrote works fine with the caveat that it's using what you call "side recursion" the same way that's necessary for DAX (which isn't as efficient for large tables). If you're going to use M for the custom column, then I think it makes sense to show a recursive solution too, which is what I provided.

I'm not trying to pick a fight. I kudoed your post and it was accepted as the answer, as appropriate. Maybe mine will help a future reader who needs a recursive solution for performance reasons.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-07-2022
09:11 AM

I get that. No hurt feelings 🙂 I appreciate your solution as well. Actually, I have saved this page to have something to return to when I'll need an efficient algorithm for the calculation of running totals. I did know about this way of writing M but was too lazy to implement it, so I went for the less efficient solution. Depending on how big the dataset is, it might do the trick. In case it's too slow, your code should do the trick. My code is probably easier to understand for the people new to Power BI or Power Query. Yours is more advanced. Much more.

Whether DAX has or not any kind of udf's is open to debate but it's not worth debating it. So, let's leave it at that.

Cheers!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-04-2022
07:31 PM

@ybyb23 , I doubt this can be done. the recursive calculation is a little bit of a challenge in DAX. As long as we can achieve them using cumulative, that can be done

Microsoft Power BI Learning Resources, 2023 !!

Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!

Announcements

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Check out the April 2024 Power BI update to learn about new features.

Top Solution Authors

User | Count |
---|---|

43 | |

22 | |

20 | |

15 | |

13 |

Top Kudoed Authors

User | Count |
---|---|

41 | |

41 | |

37 | |

19 | |

19 |