Return as aggregation of a conditional measure the result for the last completed week

[jessicarocha]

Hello, 

I have a formula that calculates the inventory turn ratio. But there is no aggregation displayed.

Ratio = 
IF(
    MAX( dim_date[date_id] )  < TODAY(),
    VAR __wdw =
        WINDOW( -51, REL, 0, REL, ALLSELECTED( dim_date[week] ), ORDERBY( dim_date[week] ) )
    RETURN
        IF(
            COUNTROWS( __wdw ) = 52,
            CALCULATE( [sum_demand] / [avg_stock], __wdw )
        )
)

I would like to see as an aggregation, the value of the formula for the most recent week that contains data. In this example, I would like to see 7.67 as an aggregation. Even if the user doesn't have any variable from the date dimension in a table or card, I still would like to see the 7.67.

Can you help me to modify the formula to achieve this?
Dummy Power BI file is available in the following link: 

Link dummy PB 

Thank you. 

[HotChilli]

The original measure is explicitly checking for 52 rows in the window.  There aren't 52 rows in the context of the card so you get a blank returned.

[jessicarocha]

@HotChilli you are right, it works for the card visual. Thank you so much for the help! 

But I still don't get an aggregation in the end of the table. 

I wanted to have the last value as the default aggregation, but if the user selected a specific week, then this value should be displayed. At the moment, if the user filter a different week, the result is empty. Do you know if there is a way to achieve this?

[HotChilli]

"it doesn't work. I think the argument has to be a column" - there are 2 arguments, one is a column, one is an expression. I think it works.

If you are writing DAX as per the measure you posted, you can solve this one. 

[HotChilli · Accepted solution]

I think you can get this with LASTNONBLANKVALUE.

Create another measure using this and pass the [Ratio] measure to it.

[jessicarocha]

@HotChilli it doesn't work. I think the argument has to be a column...
I have a power BI dummy file as a link available for download in the post, in case you want to try