Create a Filter from 8pm Yesterday through 8am today

[MA_guy_VER]

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.

[Greg_Deckler · Accepted solution]

@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 · Accepted solution]

@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])

[Greg_Deckler · Accepted solution]

@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 · Accepted solution]

@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]

@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 · Accepted solution]

@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]

@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"

[v-yueyunzh-msft]

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]

@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!

[MA_guy_VER]

@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)  

[MA_guy_VER]

@Greg_Deckler First, thank you for posting!  Please see result: