Forum Discussion

MA_guy_VER's avatar
MA_guy_VER
Icon for Advocate I rankAdvocate I
4 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 li...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    4 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
    4 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
    4 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

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
4 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
4 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
    4 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?

    • rsbin's avatar
      rsbin
      Icon for Community Champion rankCommunity Champion
      4 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

       

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

        rsbin Greg_Deckler  of course it wouldn't be.  Left is what is coming in from the data.  Right is the snippet from your last post (I took 26 days off to see the formatting on DD.  I had to add the zeros in to get it to match.  The var will most likely need some formatting or IFs as the original drops the leading zeros in the MM and DD.  Again, super appreciative of the assistance as I am still learning how to walk in PBi

         

        Measure 2 = YEAR( Today() ) & MONTH( Today() ) & DAY( Today()-26 )

         

        Measure 2 = YEAR( Today() ) & "0" & MONTH( Today() ) & "0" & DAY( Today()-26 ) & ".00"

         

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

      rsbin , Greg_Deckler did a quick measure for validation on date and it returned the results.  When using Today() it blanks.

       

      Measure = CALCULATE(SUM(PHISTTP[TPKMEN]),PHISTTP[TPDTTS] = 20220926,PBESTTP[TPTIK] >= 200000)