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 li...
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

     

v-yueyunzh-msft's avatar
v-yueyunzh-msft
Icon for Community Support rankCommunity Support
3 years ago

Hi , MA_guy_VER 

You can use this dax to try to replace the date format:

Measure 1 = YEAR( Today() )*10000  + MONTH( Today() ) *100 +  DAY( Today() ) & ".00"

 

The result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

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

v-yueyunzh-msft That worked after I removed the ".00" from the end.  Cheers!

 VAR __Today = YEAR( Today() )*10000  + MONTH( Today() ) *100 +  DAY( Today() )
 
rsbin , Greg_Deckler and V - I certainly want to make sure credit is given to the appropriate people.  Where would one recommend the Accept Solution be marked?  Thank you all so very much for the assistance!