Calculating weighted average of the prior period

[mjoost]

I have a page level filter for users to select a date (31 December 2020), the page then displays a weighted average measure (cap rate in property valuations.

I now want to show the weighted average cap rate for the preceding period as well (30 June 2020), however, my weighting is summed (i.e. appears as 1) and is not displayed on a per property basis:

DB_ClientOnlyFV_PY = 
//Provides the total portfolio fair value of only the client (i.e. excludes the market) for one period prior to the latest period
VAR currFVdate = // Determines the latest date which is assumed to be the most recent date of the valuation
    CALCULATE (
        MAX ( 'Client'[Date] ),
        FILTER ( ALL ( Client ), 'Client'[Adopted Value] > 0 )
    )
VAR priorDate = //Determines the date just before the max date
    CALCULATE(
        MAX(Client[Date]),
        FILTER(All (client),'Client'[Adopted Value] > 0 && 'Client'[Date]<currFVdate) //Filter to show the date just prior to the MAX date
    )
RETURN 
CALCULATE(
    SUM(Client[AdoptedValueFIX]),
    FILTER(
        ALL(Client),
    Client[Date]=priorDate 
    && Client[Client Name] <> "Market" //Show all properties where the property is NOT a market property
    )

)

Testing the formula up to the priorDate variable, the date returned is correct, however, the final return section contains an error and the weighting returned is 1 for all properties in the database.

Any ideas? I suspect this is an issue with my filter?

[amitchandak]

@mjoost , This does not seem like a weighted average formula. This is sum with filter.

When you work with date and want to move across use date table joined to your date

Try like with date table

DB_ClientOnlyFV_PY = 
//Provides the total portfolio fair value of only the client (i.e. excludes the market) for one period prior to the latest period
VAR currFVdate = // Determines the latest date which is assumed to be the most recent date of the valuation
    CALCULATE (
        MAX ( 'Client'[Date] ),
        FILTER ( ALL ( Client ), 'Client'[Adopted Value] > 0 )
    )
VAR priorDate = //Determines the date just before the max date
    CALCULATE(
        MAX(Client[Date]),
        FILTER(client,'Client'[Adopted Value] > 0), Filter(all(Date) , 'Date'[Date]<currFVdate) //Filter to show the date just prior to the MAX date
    )
RETURN 
CALCULATE(
    SUM(Client[AdoptedValueFIX]),
    FILTER(
        (Client),
     Client[Client Name] <> "Market" //Show all properties where the property is NOT a market property
    ), filter(all('Date'),     'Date'[Date]=priorDate )


)

[mjoost]

Thanks @amitchandak ,

The above is part of the calculation (i.e. to get the value per property). I do have a separate calculation to determine the weight (being the result of the above formula) diveded by the total fair value across the database.

The issue I am facing - the result of this formula returns the total of the portfolio, or put differently, if I had to create a table, it appears as follows, instead of showing the correspending property's value for the prior period in the table.