Why is my last quantity formula not working

[rpinxt]

I do not understand why this is not working and returns empty value:

There is only 1 timestamp in so I am really surprised this is not working.

Cannot see what I am doing wrong.

[Greg_Deckler · Accepted solution]

@rpinxt CALCULATE gets wonky with single tables sometimes. Try something like below. If that doesn't work then it is quite likely that you are running afoul of auto-exist where it doesn't even try the calculation.

LastQty Measure = 
  VAR __Max_TS = MAXX(ALL('AVN PPS Daily_LC1510'),[lc_timestamp])
  VAR __Curr_TS = MAX(''AVN PPS Daily_LC1510', [lc_timestamp])
  VAR __Result = IF(__Max_TS = __Curr_TS, [Quantity], 0)
RETURN
  __Result

[rpinxt · Accepted solution]

@Greg_Deckler just to let you know that I got things working now.

As you see also your method is working.

Not entirely sure but I started over and made sure I had a pure direct query storage mode.

First I had a mixed mode because I made a Metrics table in which I stored my measures.

It is the only thing I think changed....

However I now made my %Done card with this formula :

Thanks for looking into this with me 😄

[Greg_Deckler]

@rpinxt I wonder if the original problem was that you had a composite data model and perhaps you needed a table to be in Dual Mode but it wasn't?

[rpinxt]

Not sure what that means, but could well be.

Was very mysterious to me. In the old version it would work if I would put text in the result.

But as soon as it needed to calculate or reference a measure it went blank.

[rpinxt]

@Greg_Deckler 
Ok with a new source the problems are still there. This is so weird:

Well my endgoal with this exercise is to get the %Done. To know this I need to dive the amount with status Done by the total amount.

But of course only for the last run, so the latest timestamp.

Even with this extensive test measure if look on every VAR as expected but the Result......

You see it points to the correct line (because all the other lines have a 0.00) but it refruses to show the Quantity (which is fine 4 columns before!)

I'm lost...
I put this small file on my google drive. Maybe you (or others) can see things that I am missing here...

Link: https://drive.google.com/file/d/1BTjiyStsCOYXODWpaOUGSlD3vV2-kPyH/view?usp=sharing

[rpinxt]

@Greg_Deckler ok with a fresh trend start still not doing as expected:

Your measure still returns nothing only the 0.

My old measure for some reason does return the last quantity of that moment.

However I would expect that on the line of 8:31 not 8:01 !! 😩

Very weird.

Will make a total new data source but fear that it will not make a difference...

[rpinxt]

@Greg_Deckler  think I have the issue....

This is done on a direct query database. If I switch to import :

**bleep**...does this mean you cannot get the latest quantity in a direct query modus??

Really weird because when testing all this there were instances where I got the data just fine.

The source did crash one time....maybe it corrupted things and should try with a new sql dataload.

[Greg_Deckler]

@rpinxt What is the formula for your Quantity measure?

[rpinxt]

@Greg_Deckler well that is pretty straight forward:

Quantity = SUM('AVN PPS Daily_LC1510'[Qty])

Qty is a sumarized value in the direct query table:

[Greg_Deckler]

@rpinxt That's really quite odd. If the issue was autoexist then I would think that would surface in both DirectQuery and import mode. Quite odd that the problem only appears in DirectQuery mode as I don't see any functions that should cause problems in DirectQuery mode.

[rpinxt]

@Greg_Deckler yes it is strange.

As you see in the screens "Quantity" is just working fine so it can give amounts.

It is the filter where it goes wrong. Somehow it seems that it cannot compare the timestamps correctly.

As said there was a crash.

Tomorrow a new set of sql data will be generated (it resets every day).

Will see if that looks better.

Otherwise I will make a total new data source and see what that does.

Will post results here tomorrow.

[rpinxt]

Yes another approach with the same outcome:

The problem lies within the Filter part of the calculate, because when I take that out:

But I only want to see the first 3 lines because those are the latest timesstamp.

Do not understand why the filter is not working.

It is litteraly 2 times the same field!! lc_timestamp and max(lc-timestamp).

Makes no sense....

[rpinxt]

Bit more detail but this is really anyoing me....should work should it not??

If you do not know what the correct formula is, but still see why mine should not work please let me know!!

[Greg_Deckler · Accepted solution]

@rpinxt CALCULATE gets wonky with single tables sometimes. Try something like below. If that doesn't work then it is quite likely that you are running afoul of auto-exist where it doesn't even try the calculation.

LastQty Measure = 
  VAR __Max_TS = MAXX(ALL('AVN PPS Daily_LC1510'),[lc_timestamp])
  VAR __Curr_TS = MAX(''AVN PPS Daily_LC1510', [lc_timestamp])
  VAR __Result = IF(__Max_TS = __Curr_TS, [Quantity], 0)
RETURN
  __Result

[rpinxt]

Thanks @Greg_Deckler 
Tried it but gives same result:

Here "LastTime" compares lc_timestamp with LastTS.

So this proves that there is no difference in the timestamp and the max timestamp.

But still empty. Zeros are correct.

Apparently for the total it si working again...

But are you saying that my data is corrupted or what does "afoul of auto-exist" mean?