Iteration should work until the condition get pass

[SharmilaBrisca]

Hi Team,
I am having Three tables:
*Sales Table
*Outstanding Table
*Date Table

Scenario:
if sale of max(month) is greater than outstanding of max(month) then Count of days in a month
if sale of max(month) is lesser than outstanding of max(month) then it should find difference value which means Outstanding minus Sales(we will get some difference(d1))...now we need compare the difference with previous month sales value
if sale of max(month)-1 is greater than difference(d1) then count of days in max(month) + difference(d1) / (sales of max(month)-1/count of days in max(month)-1)
if sale of max(month)-1 is lesser than difference(d1) then again  we need to find the difference values now difference between d1 and sale of max(month)-1 (d1 - sale of max(month)-2), we will get difference(d2)...again we need to compare d2 with sale value of max(month)-2

Like this the iteration will work until the difference value becomes lesser than sale value...

Here I will attach a screenshot for better understanding  

In Power BI, I can able to implement this logic in a below way...but i have manually written the condition for 5 months..but i need this logic until the difference become lesser than sale value..

Days = 
Var D = IF([CM Sales]<[Total Outstanding],[Total Outstanding]-[CM Sales])

Var PS = CALCULATE([CM Sales],PREVIOUSMONTH('Date Table'[Date]))

Var PD = CALCULATE(MAX('Date Table'[Days in Month]),PREVIOUSMONTH('Date Table'[Date]))

Var D2 = D - PS

Var P2Sales = CALCULATE([CM Sales],DATEADD('Date Table'[Date],-2,MONTH))

Var P2Days = CALCULATE(MAX('Date Table'[Days in Month]),DATEADD('Date Table'[Date],-2,MONTH))

Var D3 = D2 - P2Sales

Var P3Sales = CALCULATE([CM Sales],DATEADD('Date Table'[Date],-3,MONTH))

Var P3Days = CALCULATE(MAX('Date Table'[Days in Month]),DATEADD('Date Table'[Date],-3,MONTH))

Var D4 = D3 - P3Sales

Var P4Sales = CALCULATE([CM Sales],DATEADD('Date Table'[Date],-4,MONTH))

Var P4Days = CALCULATE(MAX('Date Table'[Days in Month]),DATEADD('Date Table'[Date],-4,MONTH))

Var D5 = D4 - P4Sales

Var P5Sales = CALCULATE([CM Sales],DATEADD('Date Table'[Date],-5,MONTH))

Var P5Days = CALCULATE(MAX('Date Table'[Days in Month]),DATEADD('Date Table'[Date],-5,MONTH))

return

IF([CM Sales] = BLANK() && [Total Outstanding] = BLANK(),BLANK(),

IF([CM Sales] >= [Total Outstanding],

    MAX('Date Table'[Days in Month]),

IF(PS >= D,

    CALCULATE(MAX('Date Table'[Days in Month]) + (D/(PS/PD))),

IF(P2Sales>=D2,

    CALCULATE(MAX('Date Table'[Days in Month]) + PD + (D2/(P2Sales/P2Days))),

IF(P3Sales >= D3,

    CALCULATE(MAX('Date Table'[Days in Month]) + PD + P2Days + (D3/(P3Sales/P3Days))),

IF(P4Sales > D4,

    CALCULATE(MAX('Date Table'[Days in Month]) + PD + P2Days + P3Days + (D4/(P4Sales/P4Days))),

IF(P5Sales > D5,

    CALCULATE(MAX('Date Table'[Days in Month]) + PD + P2Days + P3Days + P4Days +  (D5/(P5Sales/P5Days))))))))

Please help me with your solutions.

[SharmilaBrisca]

Hi @dufoq3 , Can you please have a look on this

[bhanu_gautam]

@SharmilaBrisca , For this first create a measure

Calculate the maximum month for Sales and Outstanding:

MaxMonthSales = CALCULATE(MAX('Sales Table'[Month]))
MaxMonthOutstanding = CALCULATE(MAX('Outstanding Table'[Month]))

Calculate the sales and outstanding values for the maximum month:

SalesMaxMonth = CALCULATE(SUM('Sales Table'[Sales]), 'Sales Table'[Month] = [MaxMonthSales])
OutstandingMaxMonth = CALCULATE(SUM('Outstanding Table'[Outstanding]), 'Outstanding Table'[Month] = [MaxMonthOutstanding])

Create a measure to check the condition and perform the iteration:

ResultMeasure =
VAR MaxMonth = [MaxMonthSales]
VAR SalesMax = [SalesMaxMonth]
VAR OutstandingMax = [OutstandingMaxMonth]
VAR DaysInMaxMonth = CALCULATE(COUNTROWS('Date Table'), 'Date Table'[Month] = MaxMonth)

VAR Difference = OutstandingMax - SalesMax
VAR Iteration =
IF(SalesMax > OutstandingMax,
DaysInMaxMonth,
VAR PrevMonth = MaxMonth - 1
VAR SalesPrevMonth = CALCULATE(SUM('Sales Table'[Sales]), 'Sales Table'[Month] = PrevMonth)
VAR DaysInPrevMonth = CALCULATE(COUNTROWS('Date Table'), 'Date Table'[Month] = PrevMonth)
VAR Diff1 = Difference
VAR Result =
IF(SalesPrevMonth > Diff1,
DaysInMaxMonth + Diff1 / (SalesPrevMonth / DaysInPrevMonth),
VAR Diff2 = Diff1 - SalesPrevMonth
VAR PrevMonth2 = PrevMonth - 1
VAR SalesPrevMonth2 = CALCULATE(SUM('Sales Table'[Sales]), 'Sales Table'[Month] = PrevMonth2)
VAR DaysInPrevMonth2 = CALCULATE(COUNTROWS('Date Table'), 'Date Table'[Month] = PrevMonth2)
// Continue the iteration logic here
// You can use a recursive approach or loop until the condition is met
// For simplicity, let's assume we stop here
DaysInMaxMonth + Diff2 / (SalesPrevMonth2 / DaysInPrevMonth2)
)
Result
)
Iteration

[SharmilaBrisca]

Hi, My requirement the iteration should work automatically if the condition get fail...

[bhanu_gautam]

@SharmilaBrisca , Try using

Days =
VAR MaxMonth = MAX('Date Table'[Date])
VAR MaxDays = MAX('Date Table'[Days in Month])
VAR MaxSales = [CM Sales]
VAR MaxOutstanding = [Total Outstanding]

VAR Result =
IF(
MaxSales >= MaxOutstanding,
MaxDays,
VAR DaysInMonth = MaxDays
VAR Difference = MaxOutstanding - MaxSales
VAR MonthCounter = 1
VAR LoopResult =
WHILE(
TRUE(),
VAR PrevMonth = DATEADD(MaxMonth, -MonthCounter, MONTH)
VAR PrevSales = CALCULATE([CM Sales], PrevMonth)
VAR PrevDays = CALCULATE(MAX('Date Table'[Days in Month]), PrevMonth)
VAR NewDifference = Difference - PrevSales
VAR NewDaysInMonth = DaysInMonth + (Difference / (PrevSales / PrevDays))
RETURN
IF(
PrevSales >= Difference,
NewDaysInMonth,
VAR NewMonthCounter = MonthCounter + 1
VAR NewDifference = NewDifference
VAR NewDaysInMonth = NewDaysInMonth
RETURN
IF(
NewDifference <= 0,
NewDaysInMonth,
BLANK()
)
)
)
RETURN LoopResult
)

RETURN Result

[SharmilaBrisca]

Hi,
The measure you have returned is having error in LoopResult variable.