Forum Discussion

rpinxt's avatar
rpinxt
Icon for Solution Sage rankSolution Sage
3 years ago
Solved

Why is my last quantity formula not working

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.
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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's avatar
    rpinxt
    3 years ago

    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 😄

     

     

rpinxt's avatar
rpinxt
Icon for Solution Sage rankSolution Sage
3 years ago

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's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
3 years ago

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's avatar
    rpinxt
    Icon for Solution Sage rankSolution Sage
    3 years ago

    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.