SUMMARIZE Measure with Calculated Column

[Eddie_D]

Greetings Everyone,

I'm having some difficulty with a measure I've written that uses the SUMMARIZE function which I'm hoping someone will be able to help me with.

I'm working on some horizon IRR calculations for a portfolio of investments. This means that I'm looking to generate a table of transactions for a period that looks back X number of years from any given point in time (typically end of calendar year but can be any date).

In order to achieve this I need 3 elements:

1. An opening Fair Market Value ("FMV"),

2. A series of cash flows, and

3. A closing FMV. 

I have been able to generate elements 2 and 3 in the table that I need using a solution I found here as inspiration. However, I have been unable to generate the Opening FMV using a defined date variable. I'm unsure if it's my variable that is wrong or the way that I use the variable.

I'm using variables in my formula for StartDate and EndDate. These variables both seem to work okay when filtering the Calendar table down to the date range I need. In DaxStudio I can see a column returned with all the dates between 31/12/2016 and 31/12/2017.

I've then used ADDCOLUMNS with a conditional formula to extract the StartDate and EndDate FMVs. The EndDate variable seems to work okay in the conditional formula that calculates the Closing FMV. Unfortunately, the StartDate variable used to calculate the Opening FMV doesn't appear to work - it returns a blank.

Confusingly, if I hard-code the start date into my SUMMARIZE formula instead of referencing the variable StartDate I get the correct answer.

Any suggestions as to what I'm doing wrong?

My DaxStudio formula is:

EVALUATE
VAR EndDate =
    DATEVALUE ( "31/12/2017" ) // In the final model this will be MAX( 'Calendar'[Date] )
VAR StartDate =
    DATEVALUE (
        CALCULATE (
            DATEADD ( 'Calendar'[Date], -1, YEAR ),
            FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] = EndDate - 1 )
        )
    )
RETURN
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER (
                ALL ( 'Calendar' ),
                'Calendar'[Date] > StartDate // This StartDate variable seems to work okay
                    && 'Calendar'[Date] <= EndDate // This EndDate variable seems to work okay too
            ),
            'Calendar'[Date] 
        ),
        //Adds an FMV column with 2 values - the first on StartDate row and the second on EndDate row
        "FMV", IF (
            'Calendar'[Date] = StartDate ,
            [Cumulative FMV] * -1,
            //This formula to calculate Opening FMV doesn't work when StartDate variable is referenced but it does work when hard-coded date is used, e.g. DATEVALUE ( "31/12/2016" )
            IF (
                'Calendar'[Date] = EndDate,
                [Cumulative FMV],
                BLANK ()
            )
        )
    )

Grateful for any assistance you can offer.

Cheers,

Eddie

[v-cherch-msft]

Hi @Eddie_D

You may try to use Date Function to get the StartDate as below:

StartDate =
VAR EndDate =
    MAX ( 'Calendar'[Date] )
RETURN
    DATE ( YEAR ( EndDate ) - 1, MONTH ( EndDate ), DAY ( EndDate ) )-1

Regards,

Cherie

[Eddie_D · Accepted solution]

Hi Cherie,

Thank you for taking the time to reply.

Your formula for the StartDate variable is a lot simpler than my original formula although I think the -1 at the end of the formula should probably be deleted. However, the StartDate variable still doesn't work in this portion of the SUMMARIZE formula:

... IF (      'Calendar'[Date] = StartDate,
            [Cumulative FMV] * -1, ...

Having worked through the SUMMARIZE formula a bit more I think I have discovered why it isn't working... It seems to be due to a missing "=" operator in the FILTER portion of the formula.

When I change the original formula:

FILTER (
                ALL ( 'Calendar' ),
                'Calendar'[Date] >StartDate
                    && 'Calendar'[Date] <= EndDate
            )

to this formula:

FILTER (
                ALL ( 'Calendar' ),
                'Calendar'[Date] >= StartDate
                    && 'Calendar'[Date] <= EndDate
            )

then the Opening FMV is correctly included in the SUMMARIZE table.

I will mark this post as solved for now.

Many thanks,

Eddie