Problem with nested conditional column

[audstrich]

I have had a very frustrating problem with this...

I have four columns that are involved in the conditional column I wish to make:

Insurance/Service Date/Claim Date/Payment Date.

If they haven't paid yet, I want the column to give me the days to code, from service date to claim date.

If they have paid, I want the column to give me the days it took to pay, from claim date to payment date.

BUT if they have paid and the Insurance is "Patient," then they probably paid on the date of service, not the claim date, so I don't want a negative number, and I would like the column to give me the days to pay from service date to payment date.

Days to Code or Pay = IF(ISBLANK('Billing and Collections'[Payment Date],1.*('Billing and Collections'[Claim Date]-'Billing and Collections'[Service Date]),IF('Billing and Collections'[Insurance Group]="Patient",1*('Billing and Collections'[Payment Date]-'Billing and Collections'[Service Date]),1*('Billing and Collections'[Payment Date]-'Billing and Collections'[Claim Date])))

This formula didn't give me the correct value for some of the accounts. I've also tried to make columns for each step, use datediff instead, and just do it in excel and import only the values to power bi. It is very strange- in query editor, it seems to give correct values. but when I go to the Desktop, the values are sometimes wrong. The problem seems to be in the days to pay area. Is it because I am referencing the insurance column which has a text value? Or are the formats of my date columns not the same possibly? Or do I just need to download the most recent update? This has been such a headache! Any help would be greatly appreciated.

[jthomson · Accepted solution]

What does this do:

ubercolumn = 

var servicetoclaim = datediff([service date],[claim date])

var claimtopayment = datediff([claim date],[payment date])

var servicetopayment = datediff([service date],[payment date])

return if ([payment date]=blank(),servicetoclaim,if([Insurance]="Patient",servicetopayment,claimtopayment))

[v-juanli-msft · Accepted solution]

Hi @audstrich

You coluld add a calculated column in Power BI Desktop with DAX function instead of creating it in excel.

ubercolumn = 
var servicetoclaim = datediff([Service Date],[Claim Date],DAY)
var claimtopayment = datediff([Claim Date],[Payment Date],DAY)
var servicetopayment = datediff([Service Date],[Payment Date],DAY)
return if ([payment date]=blank(),servicetoclaim,if([Insurance]="Patient",servicetopayment,claimtopayment))

Best Regards

Maggie

[jthomson · Accepted solution]

What does this do:

ubercolumn = 

var servicetoclaim = datediff([service date],[claim date])

var claimtopayment = datediff([claim date],[payment date])

var servicetopayment = datediff([service date],[payment date])

return if ([payment date]=blank(),servicetoclaim,if([Insurance]="Patient",servicetopayment,claimtopayment))