Forum Discussion
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 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!
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 ) )
7 Replies
- lbendlin
Super User
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/1447523Thanks! 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.
- Ashish_Mathur
Hi,
Share the link from where i can download your PBI file. Ensure that your DSO measure is already written there.
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
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 ) )Great. thanks for the help!
