Forum Discussion
Approximate Beg Backlog and Approximate ending Backlog DAX not working as required
Hi, I am new to power bi. I have a scenario in which I have to calculate Approximate Beg Backlog & Approximate Ending Backlog.
The dax formulas for both are as follows:
Approximate Beg Backlog = Approximate Ending Backlog(previous Week),
Approximate Ending Backlog =Sum of Approximate Beg Backlog, Net Sales (Bookings) through Cancelations.
As you can see, the formulas are interdependent and causing circular dependency with sample dataset as follows:
3-Oct | 10-Oct | 17-Oct | |||
Approximate Beg Backlog | 0 | $59,985,386 | $548,181,595 | 54897953 | |
A | ($2,045,676) | ($2,016,818) | ($2,200,297) | ||
B | ($2,435,328) | ($2,400,974) | ($2,619,401) | ||
C | ($402,131) | ($748,999) | ($463,944) | ||
Approximate Ending Backlog | $59,985,386 | $54,818,595 | $542,897,953 | ||
| old beg | New Beglog | New Beglog | New Beglog | |
The above formula for Approximate Ending Backlog is giving the result but I have to put variables for each week. I am looking to get this loop functionality dynamically.
How to get it using dax?
2 Replies
- amitchandak
Super User
VJ_2692 , You should create a date table and measures like
CALCULATE(Sum(Table[Value]) ,DATEADD(Date[date],-7,DAY))
CALCULATE(Sum(Table[Value]) ,DATEADD(Date[date],-14,DAY))
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s I have already created these measures as variables in my dax formula. And it is working for that perticular variables only i.e Four weeks. But I am looking to get this loop functionality dynamically.
