Forum Discussion

AbbyLear's avatar
AbbyLear
Frequent Visitor
4 years ago
Solved

Measure for average DSO within a category

Hi guys,    I have a table that has the following fields.     Each row is a certain activity associated with the case (could be a "charge" or a payment received"). Case date and payer is u...
Need Help
Tips and Tricks
  • lbendlin's avatar
    lbendlin
    4 years ago

    That's pretty close to what I was about to propose as a measure:

     

    DSO = 
var a = VALUES('Table'[Case ID])
var b = ADDCOLUMNS(a,"Charge Date",var c=[Case ID] return CALCULATE(min('Table'[Case Date]),'Table'[Case ID]=c,'Table'[Type]="Charge"))
var c = ADDCOLUMNS(b,"First Payment",var c=[Case ID] return CALCULATE(min('Table'[Payment Date]),'Table'[Case ID]=c,'Table'[Type]="Payment"))
return averagex(c,DATEDIFF([Charge Date],[First Payment],DAY))

    or slightly more readable

    DSO =
VAR b =
    ADDCOLUMNS (
        VALUES ( 'Table'[Case ID] ),
        "Charge Date",
            CALCULATE (
                MIN ( 'Table'[Case Date] ),
                ALLEXCEPT ( 'Table', 'Table'[Case ID] ),
                'Table'[Type] = "Charge"
            ),
        "First Payment",
            CALCULATE (
                MIN ( 'Table'[Payment Date] ),
                ALLEXCEPT ( 'Table', 'Table'[Case ID] ),
                'Table'[Type] = "Payment"
            )
    )
RETURN
    AVERAGEX ( b, DATEDIFF ( [Charge Date], [First Payment], DAY ) )

     

AbbyLear's avatar
AbbyLear
Frequent Visitor
4 years ago

So I sort of figured this out today by doing the following. If anyone has a better/more efficient way to do this - let me know. 

 

1. Create a calculated column for # days between Payment date and Case Date

2. Create a measure called "Min DSO" = calculate(min([DSO]),ALLEXCEPT(Table,Table[Case ID]))

3. Create a measure called "Avg DSO" = AVERAGEX(values(Table[Case ID]),[Min DSO])

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
4 years ago

That's pretty close to what I was about to propose as a measure:

 

DSO = 
var a = VALUES('Table'[Case ID])
var b = ADDCOLUMNS(a,"Charge Date",var c=[Case ID] return CALCULATE(min('Table'[Case Date]),'Table'[Case ID]=c,'Table'[Type]="Charge"))
var c = ADDCOLUMNS(b,"First Payment",var c=[Case ID] return CALCULATE(min('Table'[Payment Date]),'Table'[Case ID]=c,'Table'[Type]="Payment"))
return averagex(c,DATEDIFF([Charge Date],[First Payment],DAY))

or slightly more readable

DSO =
VAR b =
    ADDCOLUMNS (
        VALUES ( 'Table'[Case ID] ),
        "Charge Date",
            CALCULATE (
                MIN ( 'Table'[Case Date] ),
                ALLEXCEPT ( 'Table', 'Table'[Case ID] ),
                'Table'[Type] = "Charge"
            ),
        "First Payment",
            CALCULATE (
                MIN ( 'Table'[Payment Date] ),
                ALLEXCEPT ( 'Table', 'Table'[Case ID] ),
                'Table'[Type] = "Payment"
            )
    )
RETURN
    AVERAGEX ( b, DATEDIFF ( [Charge Date], [First Payment], DAY ) )

 

  • AbbyLear's avatar
    AbbyLear
    Frequent Visitor
    4 years ago

    Great. thanks for the help!