topic Re: Approximate Beg Backlog and Approximate ending Backlog DAX not working as required in DAX Commands and Tips

Approximate Beg Backlog and Approximate ending Backlog DAX not working as required

VJ_2692 — Tue, 14 Mar 2023 07:53:14 GMT

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

Approximate Ending Backlog=

var date1=FIRSTDATE(MarketTable[Start MinWeek])

var addoffour_previouseweek = CALCULATE([addoffour],DATEADD(MarketTable[date],-7,DAY))

var addoffour_prepreviouseweek = CALCULATE([addoffour],DATEADD(MarketTable[date],-14,DAY))

var prepreweek = CALCULATE([addoffour],DATEADD(MarketTable[date],-21,DAY))

var pr1 = CALCULATE([addoffour],DATEADD(MarketTable[date],-28,DAY))

return

IF(date1=MIN(MarketTable[date]),[addoffour],[addoffour]+addoffour_previouseweek+addoffour_prepreviouseweek+prepreweek+pr1)

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?

Re: Approximate Beg Backlog and Approximate ending Backlog DAX not working as required

amitchandak — Wed, 15 Mar 2023 03:03:09 GMT

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

Re: Approximate Beg Backlog and Approximate ending Backlog DAX not working as required

VJ_2692 — Wed, 15 Mar 2023 08:03:56 GMT

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.