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!

 

[scottsen]

Ya, a workbook would probably help. It's pretty darn rare to call EARLIER() in a measure (far far far more common in a calculated column).

The errror is likely coming from VALUES ( 'Date'[Date] ) ... maybe from a grand total cell? Can always protect via IF (HASONEVALUE(Date[Date]), ..., BLANK())

 

[m1nkeh]

thanks scott,

i'll upload a workbook when i get into the office in a bit..

 

[m1nkeh]

ok, here is the workbook shared from onedrive... couldn't see a way to upload it here:

http://1drv.ms/1At8FgG

I've edited some of the tables a bit from the original query... just to make them a bi clearer to understand Data/Date??! and also Date i think is a reserved word so was sometimes giving me grief.

Also included is a representation of what I want to achieve.. hopefully it makes sense?

The requirement, in a nutshell is "given a salespipeline table which tracks the states of a product as it moves through time, i need to be able to see the most recent valuations (by state/product) at any given time.." up to something reasonable, not just infinite, perhaps the most recent salespipeline record would be a sensible stop point

i have excluded other dimensions such as store.. as i have 'assumed' that these will just work fine so long as we get this measure sorted out and i'm literally just splitting the measure by the stores. will i need to always include a time dimension?

thank you.

 

[scottsen]

Any chance this is... close enough?


Most Recent Value:=CALCULATE(
CALCULATE(SUM(SalesPipeline[Value]), LASTDATE(SalesPipeline[Date])),
FILTER(ALL(Dates), Dates[Date] <= MAX(Dates[Date]))
)

 

[m1nkeh]

That's better than things that i did, and perhaps almost there, but i am also trying to consider the last known status.. it's a bit tricky but i'll try to explain, this is what i was trying to do with the 'earlier' function.

Say for example a user wants to know "Products that are still in Status=Ordered", so they filter on Status, removing all other Status types.

If the Product is actually currently in one of those removed status types, it needs to not be considered... I was attempting to do this with the EARLIER() function (hacking about really) by:

1) Getting the most recent StateChangeId for a Product (Products are are unique, and more like an OrderId tbh)
2) Apply the Status filter to set 1) leaving only the Products whos most recent state is "Ordered"
3) Now 'explode' those Products (with all the earlier Status values) up to what we currently are over time as we have here..

I hope that makes sense, it's really annoying me knowing what I want to do but not being able to fully get it down into DAX syntax.

Cheers for your help!

 

[scottsen]

Not really getting what you are trying to do Does my measure NOT work when you slice by status?

 

[m1nkeh]

Not really getting what you are trying to do Does my measure NOT work when you slice by status?

i think my post was over complicating things a bit (well, a lot!).. currently the measure does work... but perhaps not as expected, although it is doing what it's meant to do.

it requires a bit more consideration... but if you consider that a product might go through some states, say just three. if i filter out two those states, and am only interested in one of them... what value (if any) should be displayed where the product was not in the state we are filtered on on a given day..

i've thought about it a bit more, and actually i think that it just needs to be not there at all, see the green highlights below.

what do you think?

 

[m1nkeh]

ok, i've got this doing what i need... but still have a bit of an issue that i'm struggling with.

basically, if i try to split this newly created measure by something... the values on the day do not aggregate to the sum total of the day..

any pointers on how to overcome this?

i think at the moment the calc is getting the 'latest date' but ignoring other dimensionality in play, i'm not having much joy at this end.

 

[scottsen]

Can you give us the current measure being used, and what fields you have on rows/columns/slicers?