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
RVoodoo
Frequent Visitor

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.

 

RVoodoo_0-1623432205596.png

 

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.

1 ACCEPTED SOLUTION

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.

View solution in original post

5 REPLIES 5
v-eqin-msft
Community Support
Community Support

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)

 LOOKUPVALUE.PNG

 

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

parry2k
Super User
Super User

@RVoodoo Thanks for providing detailed explanation. Good job!

 

It will be easier if you share a sample excel or pbix file (remove sensitive information before sharing) and provide the expected output. It will help to provide the solution.

 

You can share using google drive/one drive.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

RVoodoo_0-1623439256455.png

EDIT cuz I forgot to include the new column

RVoodoo_1-1623439776304.png

 

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.

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.