cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

How do you select the value of yesterday

I have literally been trying to figure this out for the past 4 hours.  I have tried using variables, Previousday, intervals, etc.  

 

No matter what I do I literally can not seem to select a single cell from yesterday's date.  I need to create a measure beacuse I have to use that number to do further calculations.  It should not be this difficult =[

 

I have a table that already aggregates total users and updates daily.  Just need a measure to select this value. I have a date table.

27 REPLIES 27
Bian Regular Visitor
Regular Visitor

Re: How do you select the value of yesterday

Have you tried using DATEDIFF?

https://docs.microsoft.com/en-us/dax/datediff-function-dax

Not PBI infront of me but you should be able to work it out with the use of NOW() and your date - 1.

Re: How do you select the value of yesterday

That returns a date between 2 different dates?  I just need yesterday's value

Re: How do you select the value of yesterday

NOW()-1 returns the value with a timetamp, which is going to cause issues

Bian Regular Visitor
Regular Visitor

Re: How do you select the value of yesterday

Not the best looking formula but woked when tried in a meassure:

Measure = CALCULATE(SUM(Table1[Value]),Table1[Date]=Date(YEAR(now()),Month(now()),(DAY(now())-1)))

Re: How do you select the value of yesterday

Give this one a try.

 

Yesterday = 
VAR MaxDate = MAX ( Dates[Date] )

RETURN
CALCULATE( [Amount],
    FILTER( ALL ( Dates[Date] ) , Dates[Date] = MaxDate - 1 ) 
)

yesterday.JPG

Re: How do you select the value of yesterday

nope, returns blank value =[

Bian Regular Visitor
Regular Visitor

Re: How do you select the value of yesterday

Can you post your measure?

Re: How do you select the value of yesterday

No just returns blank... Let me see if I can't just create an imgur album and add a ton of pictures of my data

Re: How do you select the value of yesterday

https://imgur.com/a/Zg4pSHo

 

The second skinnier image is the table with the values I am trying to pull from yesterday.  Imagine runstatus is "ActiveCount" as that is the actual column I am trying to grab since it automatically aggregates daily.