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

How to flag new Sales Orders

Dear All,

 

I would need your help.

 

I have a dataset with Sales Orders which are changing overtime, which means that on a daily basis there will be an inflow (new Sales Orders) and also Sales Orders that were already there in the previous days.

 

What I would like to do, is to flagged the ones that are new. Any idea?

 

Below my dataset sample:

 

Plant

Market

Day

Sales Order

SOs

Type

xyz

US

26/06/2018

111

1

CRDD<RDD

xyz

US

26/06/2018

222

1

CRDD<RDD

xyz

US

26/06/2018

333

1

CRDD<RDD

xyz

US

27/06/2018

111

1

CRDD<RDD

xyz

US

27/06/2018

222

1

CRDD<RDD

xyz

US

27/06/2018

333

1

CRDD<RDD

xyz

 

27/06/2018

444

1

LT<3 No PO

 

As you can see, Sales Order 444 was not included in yesterday’s list, and somehow I should mark it.

 

Thanks

 

Marco

 

1 ACCEPTED SOLUTION

If you can't get "Date Created" for each entry added to the data source, I don't believe PowerBI or PowerQuery can can do this natively. 

 

As workarounds you could do it in Excel (you can still use PowerQuery and DAX) but you might need a macro or VBA, Access or just manually save the data set locally in a way you have a copy of the previous source and the current source.

 

In Excel a workaround would be.

  1. use GET DATA to get your data, save to PowerPivot data model AND a Table in your spreadsheet (you can even copy your query from PowerBI PowerQuery (Edit Data) to Excel PowerQuery
  2. add a column "update date" or something similar with current date using NOW()
  3. Create your own history data table to archive or valult the data by usin copy and paste as values to a new tab
  4. write/record a macro to refresh the data then append the updated table to your new archive table (it will now have different date/time stamp in your update date column.

You could then reference that history table from power BI (get data from Excel) and create a calcuated column in DAX to set a new flag if that entry appears in your history table more than once and set NEW entry flag.

 

Its messy but could even be fully automated but your best solution is get a better data souce.

 

 

View solution in original post

4 REPLIES 4
v-yuta-msft
Community Support
Community Support

Hi Sciupo,

 

"What I would like to do, is to flagged the ones that are new."

<---What does "new" mean in your dataset, could you please clarify more details about your expected requirement?

 

Regards,

Jimmy Tao

With "New" I meant new Sales Orders which were not present in the dataset the day before.

 

My idea would be to add on a daily basis to the existing list of Sales Orders from previous days, today's Sales Orders list, and compare the data.  

 

After that, I would like to flag the Sales Orders that are present only in today's data as "New".

 

I hope this clarifies.

 

Thanks

 

Marco

If you can't get "Date Created" for each entry added to the data source, I don't believe PowerBI or PowerQuery can can do this natively. 

 

As workarounds you could do it in Excel (you can still use PowerQuery and DAX) but you might need a macro or VBA, Access or just manually save the data set locally in a way you have a copy of the previous source and the current source.

 

In Excel a workaround would be.

  1. use GET DATA to get your data, save to PowerPivot data model AND a Table in your spreadsheet (you can even copy your query from PowerBI PowerQuery (Edit Data) to Excel PowerQuery
  2. add a column "update date" or something similar with current date using NOW()
  3. Create your own history data table to archive or valult the data by usin copy and paste as values to a new tab
  4. write/record a macro to refresh the data then append the updated table to your new archive table (it will now have different date/time stamp in your update date column.

You could then reference that history table from power BI (get data from Excel) and create a calcuated column in DAX to set a new flag if that entry appears in your history table more than once and set NEW entry flag.

 

Its messy but could even be fully automated but your best solution is get a better data souce.

 

 

Seward12533
Solution Sage
Solution Sage

I can't see anyting in the data that would indicate its new. So you would need to compare the previous data with the current to extract the changes.   If there is there a date created or something like that in the data you could use that.  Do you have access copies to previous days dataset? Without manually vaulting the data I'm not sure of a way except possibly using the new feature for incrementially refreshing you data (https://docs.microsoft.com/en-us/power-bi/service-premium-incremental-refresh) to have two copies of the data set with one that refreshes once per day or every two days and compare them for changes this may only work in the service.

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.