LastEverNonEmpty

[m1nkeh]

hi,

i've a model where i need to know at a given point in time what the value of a sales pipeline is by order and the latest status of the product...

so, upon creating a simple model.. i am geting as fast as being able to implement a LastNonEmpty style calculation.. but then not getting anywhere, the best code i've got so far is:

z :=CALCULATE (
    SUM ( 'Data'[Value] ),
    FILTER (
        'Data',
        'Data'[StateChangeId]
            = CALCULATE (
                MAX ( Data[StateChangeId] ),
                ALL ( Data ),
                'Data'[Product] = EARLIER ( 'Data'[Product] ),
                'Data'[Date] <= VALUES ( 'Date'[Date] )
            )
    ),
    LASTDATE ( 'Date'[Date] )
)

after staring at various blogs from Marco and Alberto for far too long!

I don't understand why i'm getting an error message regarding "a table of multiple values was supplied"

Can provide the workbook if required...

Thanks!

 

[m1nkeh]

k, here is a link to the workbook: http://1drv.ms/1o1N7DD

we've put a wrapper around the LastNonEmpty value to do a SUMX over the product dimension... it works, but feels a bit wrong because what happens when you stick another dimension on?!

It might be the best option... but it does feel a bit 'wrong'