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

Re: How do you select the value of yesterday

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

jdbuchanan71 New Contributor
New Contributor

Re: How do you select the value of yesterday

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.

Re: How do you select the value of yesterday

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

jdbuchanan71 New Contributor
New Contributor

Re: How do you select the value of yesterday

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 ?

Re: How do you select the value of yesterday

So I just realized that in Edit Query mode it is actually perfect, but in the modeling view that I showed you it seems to be skipping dates.  When I hit the filter on "date" column in modeling mode I can see every single day in order so it is definitely a sort by column issue.  Is that bad?  

jdbuchanan71 New Contributor
New Contributor

Re: How do you select the value of yesterday

Nope, the way you have the table sorted in the data view won't do anything to calculations using the table.

Re: How do you select the value of yesterday

CALCULATE(SUM('LogTable'[RunStatus]),'LogTable'[Date]=Date(YEAR(now()),Month(now()),(DAY(now())-1)))

 

If I used my date table here why would that not work than?  Isn't it just saying the same thing as using the tables date?  I don't get it

Bian Regular Visitor
Regular Visitor

Re: How do you select the value of yesterday

CALCULATE(SUM('LogTable'[RunStatus]),'LogTable'[Date]=Date(YEAR(now()),Month(now()),(DAY(now())-1)))

 

--This part sums your values

CALCULATE(SUM('LogTable'[RunStatus]),

 

--This part checks for your dates in the table. Should also work with date table. Perhaps if you try using Related() 

'LogTable'[Date]=

 

--This part sets yesterdays date.

Date(YEAR(now()),Month(now()),(DAY(now())-1)))

 

Hope that helps.