Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Black_magic100
Helper III
Helper III

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
Helper II
Helper II

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.

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

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

nope, returns blank value =[

Can you post your measure?

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

Give this one a try.

 

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

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

yesterday.JPG

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. 

Does your date table cover the time period you are reporting on?

Yes I am just trying to grab yesterday.  My date table starts in 2014 and goes to end of 2019

my formula would look like below:

SumRunStatus = CALCULATE(SUM('LogTable'[RunStatus]),'LogTable'[Date]=Date(YEAR(now()),Month(now()),(DAY(now())-1)))
Not sure why it's empty for you.
Is runstatus number?

OMG that worked!!  Dude you saved my life haha.  Was about to punch my monitor.  I was using my calendar table the whole time, which may have been the issue??  

 

Here is the actual formula with proper column names.  Why would my date table not work?  Typically instead of 

'ClubDayStats'[LogDate] I would use 'Calendar'[Date]

No, forget RunStatus.  That should be ActiveCount.  I just included runstatus because I didn't want to reorder all the columns to show in a single jpeg.  That is just a "Placeholder".  Yes active count is a whole number.  

It looks like your date column increments in 7 day jumps.  I can see that causing problems.

 

dates_column.jpg

You know what... maybe that is causing an issue.  How do I fix that?  It is a sorting issue I presume, but not sure how to fix.  Again, I had to organize a lot of cells by monthnum otherwise they would be out of order

It depends on how your date table is built but what is happening in the measure it is filtering the date table for every date that = say 1/2/2019 and it is not finding a line for 1/2/2019 in your date column so it is returning a blank.

 

Your date column should have every date for the whole spacn so 365 rows a year.  For sorting months, you can always set the sort coumn of month name to be month number so it appears Jan, Feb, Mar in charts.

https://www.youtube.com/watch?v=BtYn1hfdSAM

 

I just copied Avi's date calendar as it was the easiest option to stay consistent versus building my own in DAX or using some other option.  

 

Here is my entire date calendar table: https://imgur.com/a/4Fj53jZ

 

What rows would I keep default and what rows would I need to sort by MonthNum?  IN Avi's video he does 3 or 4 columns at least and then cuts it so I had to use my best judgment on what columns to organize by MonthNum.  I am guessing I messed something up

I think the problem will be in the 8th step of his Calednar query.

 

Instead of = List.Dates(StartDate, Number.From(EndDate - StartDate)+1, #duration(1,0,0,0))

I think you have = List.Dates(StartDate, Number.From(EndDate - StartDate)+1, #duration(7,0,0,0))

 

Which is causing it to do 7 day jumps.

I actually do have (1,0,0,0) so it must be a sorting issue?Untitled.png

Yeah, that seemed like an odd thing for you to have changed.  Did you maybe add a filter to the end of query, something like WeekDayNum = 3 or Weekday = Wed ?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.