CALCULATE, FILTER, LASTDATE/MAX DATE is Not working

[Anonymous]

This is really weird phenomenal for me.

Here is the problem. 

Here is my code.

Debt-to-Capital = 
VAR x = SUM('Balance Sheet'[Total Debt])
VAR y = SUM('Balance Sheet'[Total Debt]) + SUM('Balance Sheet'[Total Equity])
VAR latest_date = MAX('Balance Sheet'[Date])

RETURN
    CALCULATE(DIVIDE(x,y), FILTER(('Balance Sheet'), 'Balance Sheet'[Date]=latest_date))

You would expect Power BI to give you the latest debt-to-capital of the LATEST DATE

But my result end up to be the sum of all the years rather than filtering it specifically to the LATEST DATE.

Anyone knows why this is happening?

I have tried ALL, LASTDATE, MAX whatever. The filter INSIDE my calculate function is NOT WORKING.

Here is the table that i am working with. 

My code keeps giving me 67.3%. 

But I want it to be 54.6%. BASED on the latest date. 

Can anyone help me fix this code or bug?

Thanks

[amitchandak]

@Anonymous , refer if these can help

Debt-to-Capital =
VAR latest_date = MAXX(allselected('Balance Sheet'),'Balance Sheet'[Date])

RETURN
CALCULATE(DIVIDE(SUM('Balance Sheet'[Total Debt]),SUM('Balance Sheet'[Total Debt]) + SUM('Balance Sheet'[Total Equity])), FILTER(('Balance Sheet'), 'Balance Sheet'[Date]=latest_date))

or

Debt-to-Capital =
CALCULATE(lastnonblankvalue('Balance Sheet'[Date],DIVIDE(SUM('Balance Sheet'[Total Debt]),SUM('Balance Sheet'[Total Debt]) + SUM('Balance Sheet'[Total Equity]))))

[Greg_Deckler · Accepted solution]

@Anonymous - No, I would not expect that at all. Variables are not actually variable. Variables are calculated a single time within a DAX formula when you instantiate them. So, what you are attempting to do will not work. You need to do this:

Debt-to-Capital = 
VAR latest_date = MAX('Balance Sheet'[Date])

RETURN
    CALCULATE(DIVIDE(SUM('Balance Sheet'[Total Debt]),SUM('Balance Sheet'[Total Debt]) + SUM('Balance Sheet'[Total Equity])), FILTER(('Balance Sheet'), 'Balance Sheet'[Date]=latest_date))

[Anonymous]

Thanks that works like a charm. What was the issue here? What's wrong w my variables?

[Greg_Deckler]

@dragonmaidenz - Nothing is wrong with their variables, they only calculate once, when you declare them. Therefore, not exactly "variable"...