topic Re: Fixed Charge Coverage Ratio (FCCR) in DAX Commands and Tips

Fixed Charge Coverage Ratio (FCCR)

garzajnc24 — Thu, 30 May 2024 05:37:40 GMT

Hello all.

I need assistance with this ratio measure in PowerBI.

Here is the breakdown of my measures from top to bottom:

FCCR =
CALCULATE(
([Rolling12MonthAdjustedEBITDA] - [Rolling 12 Month Cash Taxes] - [Rolling 12 Month Capital Expenditures]) / [Rolling 12 Month Interest Expense]
)

Rolling12MonthAdjustedEBITDA=

CALCULATE [Adjusted EBITDA], DATESBETWEEN (DateTable[Date],

NEXTDAY (SAMEPERIODLASTYEAR ( LASTDATE (DateTable[Date]))),

LASTDATE (DateTable[Date])

Rolling 12 Month Cash Taxes=

CALCULATE [Total Cash Taxes], DATESBETWEEN (DateTable[Date],

NEXTDAY (SAMEPERIODLASTYEAR ( LASTDATE (DateTable[Date]))),

LASTDATE (DateTable[Date])

Rolling 12 Month Capital Expenditures=

CALCULATE [Total Capital Expenditures], DATESBETWEEN (DateTable[Date],

NEXTDAY (SAMEPERIODLASTYEAR ( LASTDATE (DateTable[Date]))),

LASTDATE (DateTable[Date])

Rolling12MonthIntrest =

CALCULATE [Total CapitalExpenditures], DATESBETWEEN (DateTable[Date],

NEXTDAY (SAMEPERIODLASTYEAR ( LASTDATE (DateTable[Date]))),

LASTDATE (DateTable[Date])

Any assistance will be much appreciated!

Re: Fixed Charge Coverage Ratio (FCCR)

mark_endicott — Thu, 30 May 2024 09:10:07 GMT

garzajnc24 - I'm going to work on the assumption your problem is with the first 3 months of 2018 showing "Infinity". This is likely to do with your source data.

But also, assuming all your other calculations are correct (we have no base data to test). Instead of your FCCR measure I would use the below, it's more optimal, and it handles divide by 0 errors.

DIVIDE( ([Rolling12MonthAdjustedEBITDA] - [Rolling 12 Month Cash Taxes] - [Rolling 12 Month Capital Expenditures]), [Rolling 12 Month Interest Expense], 0 )

Perhaps if this doesnt fix your issue, you can provide some detail over what is wrong, and some sample data.

Re: Fixed Charge Coverage Ratio (FCCR)

mark_endicott — Thu, 30 May 2024 09:42:31 GMT

garzajnc24 - I've also noticed your rolling 12 month measures are missing some brackets at the end, but I'm sure you're aware of that. They should be in the format below (note I have changed the date table to match my test model):

CALCULATE ( [Total Sales], DATESBETWEEN ( 'Date'[Date], NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Date'[Date] ) ) ), LASTDATE ( 'Date'[Date] ) ) )

Re: Fixed Charge Coverage Ratio (FCCR)

garzjnc — Thu, 30 May 2024 13:38:31 GMT

Thank you, mark_endicott I will try these suggestion and provide sample source data, if that does not work. My issue is numbers are not reconciling with excel report up to rolling 12 month totals.

Re: Fixed Charge Coverage Ratio (FCCR)

garzajnc24 — Fri, 31 May 2024 13:53:18 GMT

I managed to figure out what the problem was. The issue was the source data and not the measures. Thank you.