Forum Discussion

Pooja_Dasani_98's avatar
Pooja_Dasani_98
Frequent Visitor
1 year ago

DSO Countback method (DAX Calcualtioin)

I am trying to create a DSO measure that uses the countback method in Power BI. The measure should calcualte the DSO days for the selecetd YearMonth by the user. 

This is the sample data for December 2024 - 

YY-Month

Total sales (Total PPM Revenue)

Total AR

Days

24-Dec

 $                     339,388.80

 $  1,804,162.75

31

24-Nov

 $                     203,538.00

 $  1,600,624.75

30

24-Oct

 $                     278,860.00

 $  1,321,764.75

31

24-Sep

 $                     475,688.00

 $      846,076.75

30

24-Aug

 $                     413,985.00

 $      432,091.75

31

24-Jul

 $                     325,515.00

 $      106,576.75

10

 

 

 

 

 

 

DSO

163


Here in the July month, the sales is greater than the Total AR so we calcualte 10 as number of days by Total AR/ Total Sales * 30 = 10. The DSO value for Dec 2024 is 163. 

I have written the following measure 

DSO_Countback =
VAR SelectedYearMonth = MAX('Calendar (AsOfDate)'[YearMonthnumber])
VAR ARBalance = CALCULATE([Total AR Amount], 'Calendar (AsOfDate)'[YearMonthnumber] = SelectedYearMonth)
VAR RevenueBalance = CALCULATE([Total PPM Revenue], 'Calendar (AsOfDate)'[YearMonthnumber] = SelectedYearMonth)
VAR DaysInMonth = CALCULATE(MAX('Calendar (AsOfDate)'[DaysInMonth]), 'Calendar (AsOfDate)'[YearMonthnumber] = SelectedYearMonth)

VAR DSO_Result =
    SUMX(
        FILTER(
            ALL('Calendar (AsOfDate)'),
            'Calendar (AsOfDate)'[YearMonthnumber] <= SelectedYearMonth
        ),
        VAR PrevYearMonth = 'Calendar (AsOfDate)'[YearMonthnumber]
        VAR PrevRevenue = CALCULATE([Total PPM Revenue], 'Calendar (AsOfDate)'[YearMonthnumber] = PrevYearMonth)
        VAR PrevDaysInMonth = CALCULATE(MAX('Calendar (AsOfDate)'[DaysInMonth]), 'Calendar (AsOfDate)'[YearMonthnumber] = PrevYearMonth)
       
        // Adjust AR Balance based on prior month's revenue
        VAR UpdatedARBalance = ARBalance - PrevRevenue
        VAR UpdatedRevenueBalance = PrevRevenue
        VAR UpdatedDaysCount = PrevDaysInMonth

        // DSO Calculation based on the AR vs Revenue comparison
        RETURN IF(
            UpdatedARBalance >= UpdatedRevenueBalance,
            UpdatedDaysCount,  // If AR is greater than or equal to Revenue, use full days
            (UpdatedARBalance / UpdatedRevenueBalance) * UpdatedDaysCount  // Otherwise, scale the days proportionally
        )
    )

RETURN DSO_Result


The above measure gives me the value 77,891.00. What am I doing wrong in the measure? 
Please suggest a right solution. 

Need Help

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi, does this formula work for you?

     

    DSO_Countback =
    VAR SelectedYearMonth = MAX('Calendar (AsOfDate)'[YearMonthnumber])
    VAR ARBalance = CALCULATE([Total AR Amount], 'Calendar (AsOfDate)'[YearMonthnumber] = SelectedYearMonth)
    VAR DaysInSelectedMonth = CALCULATE(MAX('Calendar (AsOfDate)'[DaysInMonth]), 'Calendar (AsOfDate)'[YearMonthnumber] = SelectedYearMonth)

    VAR DSO_Calculation =
    SUMX(
    FILTER(
    ADDCOLUMNS(
    FILTER(
    ALL('Calendar (AsOfDate)'),
    'Calendar (AsOfDate)'[YearMonthnumber] <= SelectedYearMonth
    ),
    "Revenue", [Total PPM Revenue],
    "Days", MAX('Calendar (AsOfDate)'[DaysInMonth])
    ),
    [Revenue] > 0 // Ensure we only consider months with revenue
    ),
    VAR CurrentMonthRevenue = [Revenue]
    VAR CurrentMonthDays = [Days]

    // Calculate how much of the current month's revenue is needed to cover the remaining AR
    VAR RevenueToUse = MIN(ARBalance, CurrentMonthRevenue)

    // Calculate the days attributed to this month
    VAR DaysAttributed =
    IF(
    ARBalance <= 0,
    0, // If AR is already covered, no days from this month
    IF(
    ARBalance >= CurrentMonthRevenue,
    CurrentMonthDays, // If AR is more than revenue, use all days
    (ARBalance / CurrentMonthRevenue) * CurrentMonthDays // Otherwise, prorate the days
    )
    )

    // Update the remaining AR balance
    VAR RemainingAR = ARBalance - RevenueToUse

    // Update the AR balance for the next iteration
    VAR ARBalance = RemainingAR

    RETURN DaysAttributed
    )

    RETURN
    IFERROR(DSO_Calculation, 0)

  • Pooja_Dasani_98's avatar
    Pooja_Dasani_98
    Frequent Visitor
    1 year ago

    Hi Krinec, 
    Thank you for the reply.
    This measure still gives me the wrong calculated value for DSO, with this measure the DSO value is 2015 days for selected monthyear as December2024. It should be 163 according to DSO calcualtion done in excel. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago
       
      Thank you for reaching out to Microsoft Fabric Community Forum.
      Please try the below measure:

      DSO_Countback_Mine =
      VAR SelectedYearMonth = MAX('Calendar (AsOfDate)'[yy-month])

      VAR ARBalanceStart = CALCULATE(
          SUM('Calendar (AsOfDate)'[Total AR]),
          'Calendar (AsOfDate)'[yy-month] = SelectedYearMonth
      )

      VAR SalesHistory =
          FILTER (
              ADDCOLUMNS (
                  FILTER (
                      ALL('Calendar (AsOfDate)'),
                      'Calendar (AsOfDate)'[yy-month] <= SelectedYearMonth
                  ),
                  "Revenue", CALCULATE(SUM('Calendar (AsOfDate)'[Total sales (Total PPM Revenue)])),
                  "DaysInMonth", CALCULATE(MAX('Calendar (AsOfDate)'[Days]))
              ),
              [Revenue] > 0
          )

      VAR TableWithLoop =
          ADDCOLUMNS (
              SalesHistory,
              "RunningAR",
                  VAR Revenue = [Revenue]
                  VAR Days = [DaysInMonth]
                  VAR PriorRows =
                      FILTER (
                          SalesHistory,
                          'Calendar (AsOfDate)'[yy-month] > EARLIER('Calendar (AsOfDate)'[yy-month])
                      )
                  VAR ARUsed = SUMX(PriorRows, [Revenue])
                  VAR CurrentRemaining = ARBalanceStart - ARUsed
                  RETURN
                      IF (
                          CurrentRemaining >= Revenue,
                          Days,
                          DIVIDE(CurrentRemaining, Revenue) * Days
                      )
          )

      VAR Result =
          SUMX (
              FILTER (
                  TableWithLoop,
                  VAR Revenue = [Revenue]
                  VAR Days = [DaysInMonth]
                  VAR PriorRows =
                      FILTER (
                          SalesHistory,
                          'Calendar (AsOfDate)'[yy-month] > EARLIER('Calendar (AsOfDate)'[yy-month])
                      )
                  VAR ARUsed = SUMX(PriorRows, [Revenue])
                  VAR Remaining = ARBalanceStart - ARUsed
                  RETURN Remaining > 0
              ),
              [RunningAR]
          )

      RETURN Result
       

      If your problem has been solved already,  it would be great if you could share the steps or the final solution you followed, so others facing a similar issue can benefit from it as well.

      Please mark the helpful reply and accept it as solution , it will be helpful for other members of the community who have similar problems as yours to solve it faster .

      Thank you very much for your kind cooperation!

       

      Regards,

      B Manikanteswara Reddy

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi Pooja_Dasani_98 ,

         

        We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

         

        If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

        Please don't forget to give a "Kudos " – I’d truly appreciate it!

         

        Regards,

        B Manikanteswara Reddy