Forum Discussion
New column or measure for determining data/status change between yesterday and today
I'll try to be specific - but I think if I could word this right, Google would already have helped me! I've 100% built everything I needed from lurking here until now with searches.
I need a column (or 2) or measure (anything really) to determine if someone's qualification status has changed between yesterday and today. I'm a novice so my usual path is to do a conditional check for whatever, put a 1 in another column, and use all those 1s.
For my question:
- User = the employee
- Curriculum ID = the qualification name
- Curriculum Complete = if they are qualified or not (Yes/No) (you'll also see a qualified count column that has a 1 in it if complete = Yes ... that's helped me plenty with other basic needs)
- Date (Data) is the date the data came in, each day a new spreadsheet pulls in and the date gets added to the day's data
So I need to know for each person (User) a qual status change. I need to account for the Curriculum ID as well, as in... an employee may have more than one qualification. My attempts keep getting tripped up by that.
This data is a few hundred thousand rows by now, every day everything just gets pulled in. But I'm just trying to limit to comparing yesterday with today.
I was thinking 2 columns/measures name GainedQual and LostQual that had a yes or 1 - basically for each row of data (user/qual pair)...
If Yesterday Curriculum Complete was YES and Today it's NO = LostQual
If Yesterday Curriculum Complete was NO and Today it's YES = GainedQual
If yesterday and today match, both are YES or NO, nothing changed and I don't care about it ;).
The point of all this is to get a tile (count) and list of names of anyone that changed - the tile to subscribe to if it goes over 0
Thank you kindly - happy to clarify, I know I'm describing rather poorly but I'm at the edge of my knowledge.
I was actually able to accomplish what I was after last night. I'm pretty new, and I really don't know what I'm doing so I just keep trying stuff until it works. This worked.
Yesterday Qual = LOOKUPVALUE('ERO Simple Qual (Over Time)'[Curriculum Complete],'ERO Simple Qual (Over Time)'[User & Curricula],'ERO Simple Qual (Over Time)'[User & Curricula],'ERO Simple Qual (Over Time)'[Date (Data)],'ERO Simple Qual (Over Time)'[Date (Data)]-1)But thank you for your reply. I will look into that incremental refresh, I had not heard of that yet.
5 Replies
Well, it's all sensitive data. I'll build something soon perhaps. But as I keep poking and failing, I think I can accomplish what I need with one simple thing. I've combined the USER ID and Curricula columns. So now they are all unique (per day).
EDIT cuz I forgot to include the new column
So really now, I was hoping to add a new column (YesterdayQual) that just shows me the value of Curriculum Complete from yesterday for each row... I guess using the new column since it's the unique piece.
Here's my stab to maybe show the train of thought... I'm not sure if LOOKUPVALUE is the right thing for looking up within the same table - and I don't quite know how to look for 'yesterday'
YesterdayQual = LOOKUPVALUE('ERO Simple Qual (Over Time)'[Curriculum Complete],'ERO Simple Qual (Over Time)'[User & Curricula], 'ERO Simple Qual (Over Time)'[User & Curricula], 'ERO Simple Qual (Over Time)'[Date (Data)], 'ERO Simple Qual (Over Time)'[Date (Data)]-1)I've been trying and failing at this all day, so I'm kinda done. If this isn't possible, I'll try to make dummy data a bit better next week.
Hi RVoodoo ,
According to my understanding, you want to get the value of "Curriculum Complete" from yesterday,but fail to use LOOKUPVALUE() ,right?
I think it is because [User & Curricula] column is used in LOOKUPVALUE function. Since its value is unique, value from yesterday must be different from and value on current day. In this case ,you could not find the matched value.
So please try to remove User & Curricula condition like this:
Remove User & Curricula condition = LOOKUPVALUE('ERO Simple Qual(Over Time)'[Curriculum Complete], [Date (Data)], [Date (Data)]-1)Or in case your date(data) is Discontinuous, please try this:
If date is discontinuous = var _last=MAXX(FILTER('ERO Simple Qual(Over Time)',[Date (Data)]<EARLIER('ERO Simple Qual(Over Time)'[Date (Data)])),[Date (Data)]) return LOOKUPVALUE([Curriculum Complete],[Date (Data)],_last)In addition, as you said, This data is a few hundred thousand rows by now, every day everything just gets pulled in. But I'm just trying to limit to comparing yesterday with today.
It is suggested to use Incremental Refresh so that you could reduce the amount of data that has to be refreshed.
For more information,you can refer to these articles:
Incremental refresh for datasets in Power BI - Power BI | Microsoft Docs
Configure incremental refresh for Power BI datasets - Power BI | Microsoft Docs
All You Need to Know About the Incremental Refresh in Power BI: Load Changes Only
If it does not make sense, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.😀
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.I was actually able to accomplish what I was after last night. I'm pretty new, and I really don't know what I'm doing so I just keep trying stuff until it works. This worked.
Yesterday Qual = LOOKUPVALUE('ERO Simple Qual (Over Time)'[Curriculum Complete],'ERO Simple Qual (Over Time)'[User & Curricula],'ERO Simple Qual (Over Time)'[User & Curricula],'ERO Simple Qual (Over Time)'[Date (Data)],'ERO Simple Qual (Over Time)'[Date (Data)]-1)But thank you for your reply. I will look into that incremental refresh, I had not heard of that yet.
Hi RVoodoo ,
Could you tell me if my post helps you a little? If it is, please kindly Accept it as the solution as well. Thanks in advance.😀
Best Regards,
Eyelyn Qin
