Forum Discussion

MA_guy_VER's avatar
MA_guy_VER
Icon for Advocate I rankAdvocate I
3 years ago
Solved

Create a Filter from 8pm Yesterday through 8am today

Hello Community,

 

In the following data set from a HISTORY TABLE, I have operations that are picking items from 8pm (200000) the previous day (yesterday) through 8am (80000) today.  

I would like to create a Card that shows the QTY PICKED only in that dynamic timeframe.  Any assistance would be most helpful!  Thank you.

 

 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    MA_guy_VER Try:

    Measure =
  VAR __Today = TODAY()
  VAR __Yesterday = __Today - 1
  VAR __Table = FILTER('Table', ( [Date] = __Yesterday && [Time] >= 80000 ) || ( [Date] = __Today && [Time] <= 200000) )
RETURN
  SUMX(__Table,[Qty Picked])
  • rsbin's avatar
    rsbin
    3 years ago

    Greg_Deckler , MA_guy_VER ,

    I think Greg got the hours reversed.  Please re-try with this:

    Measure =
  VAR __Today = TODAY()
  VAR __Yesterday = __Today - 1
  VAR __Table = FILTER('Table', ( [Date] = __Yesterday && [Time] >= 200000 ) || ( [Date] = __Today && [Time] <= 80000) )
RETURN
  SUMX(__Table,[Qty Picked])

     

  • rsbin's avatar
    rsbin
    3 years ago

    MA_guy_VER ,

    Yes, we are assuming your [Date] Column is a Date Field.  It looks to be more like a DateKey.

    So, in this case we need to convert the VAR _Today into a similar DateKey:

    VAR _Today = YEAR( Today() ) & MONTH( Today() ) & DAY( Today() )  
VAR _Yesterday = _Today - 1

    Ensure your Date Column above is numeric

     

9 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    MA_guy_VER Try:

    Measure =
  VAR __Today = TODAY()
  VAR __Yesterday = __Today - 1
  VAR __Table = FILTER('Table', ( [Date] = __Yesterday && [Time] >= 80000 ) || ( [Date] = __Today && [Time] <= 200000) )
RETURN
  SUMX(__Table,[Qty Picked])
    • rsbin's avatar
      rsbin
      Icon for Community Champion rankCommunity Champion
      3 years ago

      Greg_Deckler , MA_guy_VER ,

      I think Greg got the hours reversed.  Please re-try with this:

      Measure =
  VAR __Today = TODAY()
  VAR __Yesterday = __Today - 1
  VAR __Table = FILTER('Table', ( [Date] = __Yesterday && [Time] >= 200000 ) || ( [Date] = __Today && [Time] <= 80000) )
RETURN
  SUMX(__Table,[Qty Picked])

       

      • MA_guy_VER's avatar
        MA_guy_VER
        Icon for Advocate I rankAdvocate I
        3 years ago

        Greg_Deckler , rsbin Same result.  Would it have anything to do with the format of the Today ()  mm/dd/yyyy hh:mm:ss am  vs what is being written to the database 20220926?