Forum Discussion

SharmilaBrisca's avatar
SharmilaBrisca
Resolver I
1 year ago

Iteration should work until the condition get pass

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...
Need Help
Tips and Tricks
bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

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's avatar
SharmilaBrisca
Resolver I
1 year ago

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

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    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's avatar
      SharmilaBrisca
      Resolver I
      1 year ago

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