Measure for average DSO within a category

[AbbyLear]

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 unique to each case ID. 

I'm trying to create a measure called "DSO" (days sales outstanding) which calculates the days between the case date and the first payment date for each case. Then I want to run some visuals that shows the average per case DSO for each payer, each quarter at the case ID level. 

For example, I want to be able to create a Matrix in PowerBI with the following information: 

For the first row here, 23.5 is arrived as the average DSO for case #1 (19) and #2 (28). 

I was able to get DSO by doing a simple "Datediff" formula. However, not sure how to get it to calculate the average per case by payer and by quarter? Any help would be appreciated! 

Thanks! 

[AbbyLear]

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 · Accepted solution]

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]

Great. thanks for the help! 

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.  Ensure that your DSO measure is already written there.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[AbbyLear]

Thanks! I followed the tutorial and pasted the data below. Let me know if this doesn't work! 

Case ID	Payer	Case Date	Case Quarter	Type	TxID	Payment Date	Payment$	Charge$
1	Aetna	1/1/2021	2021 Q1	Charge	1	null	null	$200
1	Aetna	1/1/2021	2021 Q1	Payment	2	1/20/2021	$10
1	Aetna	1/1/2021	2021 Q1	Payment	3	1/31/2021	$20
2	Aetna	2/1/2021	2021 Q1	Charge	4	null	null	$400
2	Aetna	2/1/2021	2021 Q1	Payment	5	3/1/2021	$200
2	Aetna	2/1/2021	2021 Q1	Payment	6	4/1/2021	$100
2	Aetna	2/1/2021	2021 Q1	Payment	7	5/1/2021	$50
3	Cigna	4/1/2021	2021 Q2	Charge	8	null	null	$300
3	Cigna	4/1/2021	2021 Q2	Payment	9	4/25/2021	$100
4	Aetna	5/1/2021	2021 Q2	Charge	10	null	null	$1,000
4	Aetna	5/1/2021	2021 Q2	Payment	11	5/15/2021	$200
4	Aetna	5/1/2021	2021 Q2	Payment	12	5/30/2021	$300
4	Aetna	5/1/2021	2021 Q2	Payment	13	6/30/2021	$400

expected outcome:

Payer	Case Quarter	DSO
Aetna	2021 Q1	23.5
Aetna	2021 Q2	14
Cigna	2021 Q1	24

[Ashish_Mathur]

Hi,

How did you arrive at the 19 and 28.  Please show those calculations in an MS Excel file.