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.
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?
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.
@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
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.
User | Count |
---|---|
56 | |
27 | |
23 | |
15 | |
11 |
User | Count |
---|---|
78 | |
63 | |
47 | |
17 | |
12 |