Summing Issue with Measure

CM_Mills , Try a new measure like

calculate(lastnonblankvalue(Table[Date - Trade], Sum(Table[Open Interest - Lots])) , filter(allselected(Table), Table[Data] =max(Table[Data]) && format(Table[Date - Trade],"YYYYMM") =format(max(Table[Data]),"YYYYMM")))

or

calculate( Sum(Table[Open Interest - Lots]) , filter(allselected(Table), Table[Data] =max(Table[Data]) && format(Table[Date - Trade],"YYYYMM") =format(max(Table[Data]),"YYYYMM") && [Date - Trade] =max(Table[Data])))

Thank you for lookin at this amitchandak . Looking at those measure though, they do not take into account the the 'unique Identifer'. Does that matter?

CM_Mills , I taken wrong names

calculate(lastnonblankvalue(Table[Date - Trade], Sum(Table[Open Interest - Lots])) , filter(allselected(Table), Table[Unique Identifier] =max(Table[Unique Identifier]) && format(Table[Date - Trade],"YYYYMM") =format(max(Table[Data]),"YYYYMM")))

or

calculate( Sum(Table[Open Interest - Lots]) , filter(allselected(Table), Table[Unique Identifier] =max(Table[Unique Identifier]) && format(Table[Date - Trade],"YYYYMM") =format(max(Table[Data]),"YYYYMM") && [Date - Trade] =max(Table[Data])))

Hi, CM_Mills 

It’s my pleasure to answer for you.

According to your description,I think you can change your measure.

Like this:

Measure = 
VAR A =
    CALCULATE (
        MAX ( 'Date - Trade Calendar'[Date - Trade] ),
        FILTER (
            ALL ( Data ),
            Data[Unique Identifier] = MAX ( Data[Unique Identifier] )
                && FORMAT ( Data[Date - Trade], "YYYYMM" )
                    = FORMAT ( MAX ( 'Date - Trade Calendar'[Date - Trade] ), "YYYYMM" )
        )
    )
RETURN
    IF (
        MAX ( 'Date - Trade Calendar'[Date - Trade] )
            IN VALUES ( Data[Date - Trade] )
                && MAX ( Data[Open Interest - Lots] ) <> BLANK (),
        IF (
            SELECTEDVALUE ( Data[Date - Trade] ) = A,
            CALCULATE (
                SUM ( Data[Open Interest - Lots] ),
                FILTER (
                    ALL ( Data ),
                    [Unique Identifier] = SELECTEDVALUE ( Data[Unique Identifier] )
                        && [Date - Trade] = A
                )
            ),
            0
        )
    )

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-janeyg-msft 

Hi Janey,

Thank you for taking a look. This measure again works when looking at the daily numbers.

However, when looking at this on a month view it doesn't work. It is just brings back 0.

Do yo uhave any ideas why this isn't being treated as a number and summing?

Thanks

Chris

v-janeyg-msft 

Thank you. I'm new to power Bi and DAX so please forgive my misunderstanding.

I have created the month column within the 'Date - Trade' table.

I have then created the new summerize table.  Should i join make an 'active relationship' to the data table via 'unique identifier'. When i have tried this the link ins't working right, it just bring back every identifer.

Where does 'measure 2' go? Does this get created in the new 'table'? i have tired this but not really understanding how it works.

Thanks again for your help

v-janeyg-msft massive Thank you, this works

CM_Mills 

Apologies upfront since I see a solution has been offered by v-janeyg-msft while I was working on an alternative.

Allow me to include it anyway, since it might be of benefit for others (even if just as a practice exercise)

First the model

The measure to get the last value by ID and month:

Month end value = 
VAR EOMDate = //to calculate the last date by month and ID
    MAXX (
        FILTER (
            ALL ( FactTable ),
            FactTable[Unique Identifier] = SELECTEDVALUE ( 'ID Table'[ID] )
                && MONTH ( FactTable[Date - Trade] )
                    = SELECTEDVALUE ( 'Calendar Table'[Month Numb] )
        ),
        FactTable[Date - Trade]
    )
RETURN
    CALCULATE (
        [Sum Open Interests],
        FILTER ( 'Calendar Table', 'Calendar Table'[Cal Date] = EOMDate )
    )

and you get this: