Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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:
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 ) )
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:
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
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.
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?
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 ?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |