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 Decem...
Need Help
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

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi Pooja_Dasani_98 ,

         

        As we haven’t heard back from you, 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