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
Saxon10
Post Prodigy
Post Prodigy

Compare data weekly basis based on date and folder

 

Hi,

 

Our daily sales data stored in Excel under the sales folder. I am trying to compare "Monday Last week's Sales" with "Monday this week's Sales" based on the date. What are the item are newly added (unique) from Monday last week sales compare to Monday this week's Sales. I don’t want to change the file reference manually every week. How I can automate these process in Power BI.

Can you please advise the best alternative way? Could you please attaché the final result file with snapshot because I am very to new to Power BI so it will help to understand the Power BI function.

 

The file name is always standard TRPP_EXTRACT, only the date and time will be changed on daily basis. The data contain two headers are item and sales area. The columns data(line items) can be unique some of the file and some of the file duplicated.

 

File path.

 

https://www.dropbox.com/s/mn30ztfm9szp32j/COMPARE%20WEEKS.PNG?dl=0

 

 

Example of Result.

 

https://www.dropbox.com/s/mn30ztfm9szp32j/COMPARE%20WEEKS.PNG?dl=0

 

 

Files;

 

https://www.dropbox.com/s/5sts1iv2s9uq72n/TRPP_EXTRACT_2020_11_16_08-01-20.xlsx?dl=0

 

https://www.dropbox.com/s/h33ej33iow4oeag/TRPP_EXTRACT_2020_11_09_08-00-18.xlsx?dl=0

 

 

 

8 REPLIES 8
Saxon10
Post Prodigy
Post Prodigy

Hi. 

can you please advise anyone is there any alternative way? 

Hi @Saxon10 ,

If I understand correctly, you receive an excel file about sales every week. You need to compare the item information of this week and last week to get the new added items. You can rename the files of this week and the files of last week with a special name, such as: TRPP_EXTRACT_Thisweek and TRPP_EXTRACT_Lastweek. Then connect these two files in Power BI Desktop and create a calculated table to get the new added items. You can find the details in the attachment.

rename.JPG

Final Result = EXCEPT('DATA Extract (2)','DATA Extract')

Compare data weekly basis based on date and folder.jpg
If it is coming to another new week, change the previous file name as "TRPP_EXTRACT_Lastweek" back to "TRPP_EXTRACT_YYYY_MM_DD_XX_XX_XX", the file name as "TRPP_EXTRACT_Thisweek" to "TRPP_EXTRACT_Lastweek", and the file for the latest week as "TRPP_EXTRACT_Thisweek". The specific details are as follows, assume that we are on the week which start on Nov 23, 2020:

The original file name Rename as (Current) Rename as (Next week)
TRPP_EXTRACT_2020_11_16_08-01-20 TRPP_EXTRACT_Lastweek TRPP_EXTRACT_2020_11_16_08-01-20
TRPP_EXTRACT_2020_11_23_08-01-21 TRPP_EXTRACT_Thisweek TRPP_EXTRACT_Lastweek
TRPP_EXTRACT_2020_11_30_08-01-22   TRPP_EXTRACT_Thisweek

Every new week, just need to refresh the report to get the latest new added items after renaming the file.

Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi,

Thanks for your reply. I understand your solution. I will aspect your solution as a temporary.

The sales data stored in excel on daily basis under the "Sales folder" , I need to find the weekly sales file and renaming its long process so is there any alternative way for automatic process? Can you please advise.

Hi @Saxon10 ,

Please review the method in the following blogs and check whether they can help achieve your requirement.

Getting data from the latest file in a folder using Power Query

Get data from folder and append

Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi. Thanks for your valubale time again.

 

I am aware of the following method Getting data from the latest file in a folder using Power Query.

The problem is I have a empty columns (1 st column) and top of the row.

 

Can you please help me the second option based on my actual data (  Get data from folder and append). 

 

amitchandak
Super User
Super User

@Saxon10 , Create columns like these in your date table 

Work Day = if(WEEKDAY([Date],2)>=6,0,1)

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)

 

Week wise measure

This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
Last 8 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-8 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
last two weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]<=max('Date'[Week Rank])-1

 

Only Monday

This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank]) && 'Date'[Work Day] =1))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1 && 'Date'[Work Day] =1))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Hi,

there is no date in my data file. The date only showing on the file name. Can you please advise how can I achieve my output.

thanks for very quick respones. I will try and come back to you.

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.