recursive

6 Topics

Recursive Year-over-Year Calculation in Power BI DAX
Hello everyone, I'm working on a Power BI report and I'm facing a challenge with a recursive calculation for adjusted revenues. I need to calculate the adjusted revenue for the current year based on the previous year's adjusted revenue, factoring in a growth rate percentage that can be dynamically adjusted by the user through a slicer. Here's the scenario: I have a table named Global with a column [Expenses] representing the total revenues for each year. There's a slicer allowing the user to adjust the growth rate percentage, which is represented by the measure [% Growth]. I want to calculate the adjusted revenues such that for years up to and including 2024, it simply takes the sum of [Expenses]. For years after 2024, it should take the previous year's adjusted revenue and multiply it by (1 + [% Growth]). The challenge is to reference the adjusted revenue of the previous year in a measure, which seems to require a recursive calculation. Here's the DAX formula I've been trying to use: AdjustedRevenues = VAR defvalue = SUM('Global'[Expenses]) RETURN IF( MAX('Years filter'[Year]) <= 2024, CALCULATE(defvalue), CALCULATE([AdjustedRevenues], DATEADD('Years filter'[Year], -1, YEAR)) * (1 + [% growth]) ) % growth = (sum('Global'[growth])*100+[AdjustementvalueRevenues% Value])/100 However, I'm aware that DAX does not support direct recursion. I'm looking for a way to achieve this year-over-year calculation without recursion. How can I structure my DAX formula or data model to calculate the adjusted revenues correctly? Any help or guidance would be greatly appreciated!
Solved
menphis21
1 year ago Place DAX Commands and Tips
3.9KViews
0likes
18Comments
Recursive measure challange in DAX. From Excel to Power BI
Hi all, I have a challange to move a report from Excel to Power BI due to the following measure. For cell C3 the formula is as follows "=IF(OR(COUNTIF(B3:B8;"<"&C2)=6;COUNTIF(B3:B8;">"&C2)=6);AVERAGE(B3:B8);C2)" For the next cells in "C" column it's the same formula. The only exception is cell "C2" where the formula is just "=AVERAGE(B2:B7)". So my main challange is with formula "=IF(OR(COUNTIF(B3:B8;"<"&C2)=6;COUNTIF(B3:B8;">"&C2)=6);AVERAGE(B3:B8);C2)" because in order to calculate value for "C3" I need to calculate what was calculated in "C2" thus I think it's a Recursive problem which Power BI is not especially good at. But perhaps there is some workaround to do this. I appreaciate any advises.
Solved
Yellow_hill
2 years ago Place DAX Commands and Tips
860Views
0likes
3Comments
Help with DAX Measures on Parent Child tables recursively
Hi Team, I have two tables, Pipelines and Activities. Pipeline Table has ParentPipelineId's, I want to create below measures by travelling recursively at the root level. I have created basic measurse but need help on recursive part. Please help Measues: # of Automate ==> Example: For PipelineType ==> A => We want to travel till root (child) and consider count all the activities, in this case it should Activities For 123 (3) + Activities for 456 (2) + Activities for 789 (2) # of Manual ==> Same Behaviour as Automate by TaskType will be Manual # of Unique Activities => We want to travel till root and consider distinct Activity ID Example: For Pipeline TypA, Activity ID 1 is repeating in both PipelineId 123 and 789, we want to count as 1. Relationship: Pipelines ( PipelineId - 1) <-------- Activities ( PipelineId --*) Thanks, Abhiram
abhiram342
2 years ago Place DAX Commands and Tips
873Views
0likes
2Comments
Getting total value per day based on most recent status
I have two tables. A date table with a date column A table with data showing: StatusDateChange,UserId,NewState For each date from the date column, I would like to count for each status group the count of users that fall in that status. Basically what is the most recent status up to each date of the row context. For example, if someone was: Awake on Jan 1st 2020 Asleep on Jan 3rd 2020 Sleepwalking on Jan 10 2020 I want my matrix to show case the following Date Awake Asleep SleepWalking 1/1/2020 1 0 0 1/2/2020 1 0 0 1/3/2020 0 1 0 1/4/2020 0 1 0 1/5/2020 0 1 0 1/6/2020 0 1 0 1/7/2020 0 1 0 1/8/2020 0 1 0 1/9/2020 0 1 0 1/10/2020 0 0 1 Obviously what i currently have when i activate the Show no data option from the date column Date Asleep Awake SleepWalking 1/1/2022 1 1/2/2022 1/3/2022 1 1/4/2022 1/5/2022 1/6/2022 1/7/2022 1/8/2022 1/9/2022 1/1/2022 1 How do i fill in the gaps ? One important thing to note is that I don't want to cross join my table with every date possibility even though it would work since my table would generate over 100M rows by doing this.
Solved
RogerSteinberg
5 years ago Place DAX Commands and Tips
911Views
1like
2Comments
Need help with Daily Resource Loading for Production Elements (DAX to calculate load per day)
Hello, From our ERP system I have data on: [Resource Name] (Machine Centre) [Project Name] [Start Date] [End Date] # of [Hrs of Work] required I am trying to make a stacked bar chart that shows each day, with a slicer to filter per [Resource Name], showing which projects are being processed on on the days shown. Right now I can make a stacked bar with all of the work showing up on the [End Date], but what I need to do is divide the [Hours of work] between the [start date] and the [end date], thus showing how much work is to be done on each project per day. Thanks,
Solved
nick_SMTC
5 years ago Place DAX Commands and Tips
1.3KViews
0likes
3Comments
Recursive calculation with cumulated sums
Hello all, I am struggling in Power Bi to compute the following formula (remain to engage) RTE(m) = MAX [ sum[TSP] (m0 to m) - sum[AEFP] (m0 to m) -sum[RTE] (m0 to m-1) ; 0] see example below would you have any idea to solve this please ? thank you for your support regards gen Month Total Spend planned (TSP) Already engaged for payment (AEFP) Remain to engage (RTE) 1 200 180 20 2 100 80 20 3 150 30 120 4 100 10 90 5 200 5 195 6 50 50 total 800 305 495
Anonymous
5 years ago Place DAX Commands and Tips
888Views
0likes
2Comments