Forum Discussion

Saxon10's avatar
Saxon10
Post Prodigy
5 years ago

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

 

 

 

Need Help
Tutorial Requests

8 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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.

    • Saxon10's avatar
      Saxon10
      Post Prodigy
      5 years ago

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

    • Saxon10's avatar
      Saxon10
      Post Prodigy
      5 years ago

      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.

  • Saxon10's avatar
    Saxon10
    Post Prodigy
    5 years ago

    Hi. 

    can you please advise anyone is there any alternative way? 

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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.

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


      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.

      • Saxon10's avatar
        Saxon10
        Post Prodigy
        5 years ago

        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.