Week on Week Comparison

[paris]

Hi All,

Could you plesae help me to compare week on week?

Week number 34 total is 63, Week number 35 total is 35. I want to get  -28 for week number 35.

All the best, 

Paris

[reibalboaOMG]

@edhans ow okay thanks for this, i'll start a new thread

thanks

[reibalboaOMG]

hi @edhans @amitchandak @paris everyone i'm also having a problem.

as you can see the week of update is jan 22 - feb 12 and the end date is Jan 31

this is my sample data in powerbi my formula is 

weeknumber = WEEKNUM('Template (2)'[Campaign End Date] - 'Template (2)'[Week of Update])

output: 22 : 2 29 :1 but i need at the february is 100% because it is post to the end date 

for example january 22 should be 43,000 / 2

january 29 43,000/1 

feb 5 and 12 should be 43,000 /1 it has reach it's maximum because the end date is january 31

thanks for the response 🙂

[edhans]

You should start a new thread @reibalboaOMG and provide some really clear and data. I can barely see those images and I am not sure what the expected result is. And I cannot use images as source data. All info to post usable data is below;

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[Sapiec]

@edhans Your solution is very useful to a similar problem of mine, and I am very close to applying it.

I am looking to add another layer of filtering, where the WoW Change would also calculate by territory.

here below, I have the expected result and what actually happens.

On a total sales view your script works perfect

When i add a country slicer, I would expect to get the green cells instead I am getting the red ones. The arrow shows the calculation PBI does. In stead of comparing Country X weekly sales of 57010 - 26657 = 30353, it compares to total weekly sales of 2501074.

Your help is much appreciated. 

Thank you

Carlo

[edhans]

Hi @paris - can you please mark one or both solutions as the one that helped you, and give kudos (thumbs up) to posts that also assisted. That way people will know the solution if they are searching the forum later on. Thanks!

[edhans · Accepted solution]

Hi @paris ,

You can do this with a measure and a good date table. The measure is here:

Week over Week Change = 
VAR varCurrentDate = MAX('Date'[Date])
VAR varCurrentWeek = MAX('Date'[Year Week])
VAR varPreviousWeek = 
    MAXX(
        FILTER(
            ALL('Date'[Date], 'Date'[Year Week]),
            'Date'[Date] = varCurrentDate - 7
        ),
        'Date'[Year Week]
    )
VAR varCurrentWeekTotal = [Grand Total]
VAR varPreviousWeekTotal = 
    CALCULATE(
        SUM('Table'[Total]),
        'Date'[Year Week] = varPreviousWeek,
        REMOVEFILTERS('Date')
    )
RETURN
    if(
        ISBLANK(varCurrentWeekTotal),
        BLANK(),
        varCurrentWeekTotal - varPreviousWeekTotal
    )

This is the [Grand Total] measure that calculates the weekly amounts.

Grand Total = 
SUMX(
    VALUES('Date'[Year Week]),
    CALCULATE(
        SUM('Table'[Total]),
        REMOVEFILTERS('Date'[Date])
    )
)

Your date table needs a field that is the Year and Week, so 202001, 202002, etc. through 202052/53 depending on the year.

It returns a week over week change as shown below, so for week 35, this weeks total is 47, last week is 35, so the WoW change is 12, and so on

Or by day:

this is the date table I use Way more columns than needed for this example, but a pretty comprehensive date table. This is created in Power Query:

1. Open Power Query
2. Create a new Blank Query (New Source, Blank Query)
3. Open the Advanced Editor
4. Paste the below code in, removing the 4 basic lines in a blank query
5. Closing it, then rename it Date
6. Mark it as a date table in Power BI once it loads.
7. More complete info about this date table here.

let
    Source = {Number.From(#date(2015,1,1))..Number.From(#date(2020,12,31))},
    // Change this to the first day of the week for your model. All formulas that have a First day of Week parameter will use this value. Day.Sunday, Day.Monday, Day.Tuesday, etc.
    varFirstDayOfWeek = Day.Sunday,
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type date}}),
    // Only keep and use this field if you need the YYYYMMDD date format as an integer to link to your ERP or other system. Delete otherwise.
    #"Added DateKey" = Table.AddColumn(#"Changed Type", "DateKey", each Date.Year([Date]) * 10000 + Date.Month([Date]) * 100 + Date.Day([Date]), Int64.Type),
    // Only keep this index DateID if you have a non-standard year (a year that doesn't end on Mar 31, Jun 30, Sep 30, Dec 31) and will be building your own Date and Time Intelligence functions making extensive use of FILTER and CALCULATE. Delete otherwise.
    #"Added DateID" = Table.AddIndexColumn(#"Added DateKey", "DateID", 1, 1, Int64.Type),
    #"Added Year" = Table.AddColumn(#"Added DateID", "Year", each Date.Year([Date]), Int64.Type),
    #"Added Year End" = Table.AddColumn(#"Added Year", "Year End", each Date.EndOfYear([Date]), Date.Type),
    #"Added Month" = Table.AddColumn(#"Added Year End", "Month", each Date.Month([Date]), Int64.Type),
    #"Added Month Name" = Table.AddColumn(#"Added Month", "Month Name", each Date.MonthName([Date]), type text),
    // Format as mmm in the model to get a Month that can be used in a continuous line chart.
    #"Added Month Only" = Table.AddColumn(#"Added Month Name", "Month Only", each #date(1900, Date.Month([Date]), 1), type date),
    #"Added Short Month Name" = Table.AddColumn(#"Added Month Only", "Short Month Name", each Text.Start([Month Name],3), type text),
    #"Added Month Year" = Table.AddColumn(#"Added Short Month Name", "Month Year", each [Short Month Name] & " " & Text.From([Year]), type text),
    #"Added Month Year Sort" = Table.AddColumn(#"Added Month Year", "Month Year Sort", each [Year]*100 + [Month], Int64.Type),
    // Format as mmmm yyyy in the model to get a Month Year that can be used in a continuous line chart.
    #"Added Month End" = Table.AddColumn(#"Added Month Year Sort", "Month End", each Date.EndOfMonth([Date]), type date),
    #"Added Quarter Number" = Table.AddColumn(#"Added Month End", "Quarter Number", each Date.QuarterOfYear([Date]), Int64.Type),
    #"Added Quarter" = Table.AddColumn( #"Added Quarter Number","Quarter", each "Qtr " & Text.From([Quarter Number]), type text),
    #"Added Quarter Year Sort" = Table.AddColumn(#"Added Quarter", "Quarter Year Sort", each [Year] * 10 + [Quarter Number], Int64.Type),
    #"Added Quarter Year" = Table.AddColumn(#"Added Quarter Year Sort", "Quarter Year", each "Q" & Text.End([Quarter],1) & " " & Text.From([Year]), type text),
    #"Added Quarter End" = Table.AddColumn(#"Added Quarter Year", "Quarter End", each Date.EndOfQuarter([Date]), type date),
    #"Added Week Starting Date" = Table.AddColumn(#"Added Quarter End", "Week Starting Date", each Date.StartOfWeek([Date], varFirstDayOfWeek), type date),
    #"Added Week Ending Date" = Table.AddColumn(#"Added Week Starting Date", "Week Ending Date", each Date.EndOfWeek([Date], varFirstDayOfWeek), type date),
    #"Added Week of Year" = Table.AddColumn(#"Added Week Ending Date", "Week of Year", each Date.WeekOfYear([Date], varFirstDayOfWeek), Int64.Type),
    #"Added Week of Month" = Table.AddColumn(#"Added Week of Year", "Week of Month", each Date.WeekOfMonth([Date], varFirstDayOfWeek), Int64.Type),
    #"Added Day" = Table.AddColumn(#"Added Week of Month", "Day", each Date.Day([Date]), Int64.Type),
    #"Added Day of Week" = Table.AddColumn(#"Added Day", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
    #"Added Day of Year" = Table.AddColumn(#"Added Day of Week", "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
    #"Added Day Name" = Table.AddColumn(#"Added Day of Year", "Day Name", each Date.DayOfWeekName([Date]), type text),
    // Format as ddd in the model to get a Month Year that can be used in a continuous line chart.
    #"Added Day Only" =
        Table.AddColumn(
            #"Added Day Name",
            "Day Only",
            each
                let
                    varFirstDay = varFirstDayOfWeek,
                    varWeekDay = Date.DayOfWeek([Date], varFirstDay)
                in
                /*  Note: you cannot validate this logic in Excel. It has a Leap Year bug that dates back to the mid-80's for
                    Lotus 1-2-3 compatibility. If you want to copy this table into a spreadsheet and validate the weekdays, use
                    Google Sheets
                    */
                #date(
                    1900,
                    1,
                    varWeekDay +
                    (if varWeekDay < varFirstDay + 1 then 7 else 0)
                ),
                type date
            ),
    #"Added IsFuture Boolean" = Table.AddColumn(#"Added Day Only", "IsFuture", each [Date] > DateTime.Date(DateTime.LocalNow()), type logical),
    #"Added IsInCurrentWeek" = Table.AddColumn(#"Added IsFuture Boolean", "IsInCurrentWeek", each Date.IsInCurrentWeek([Date]), type logical),
    #"Added IsInCurrentMonth" = Table.AddColumn(#"Added IsInCurrentWeek", "IsInCurrentMonth", each Date.IsInCurrentMonth([Date]), type logical),
    #"Added IsInCurrentQuarter" = Table.AddColumn(#"Added IsInCurrentMonth", "IsInCurrentQuarter", each Date.IsInCurrentQuarter([Date]), type logical),
    #"Added IsInCurrentYear" = Table.AddColumn(#"Added IsInCurrentQuarter","IsInCurrentYear", each Date.IsInCurrentYear([Date]), type logical),
    #"Added Year Week" = Table.AddColumn(#"Added IsInCurrentYear", "Year Week", each [Year] * 100 + [Week of Year], Int64.Type)
in
    #"Added Year Week"

My full PBIX file is here if you want to look at it. My sample data is stuff I keyed in. I couldn't use the data in your image. Need text/table of data, not PNG to paste into Power BI.

[paris]

Thank you very much! 

[amitchandak]

@paris , refer to my Week vs Week blog that can help

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

You need columns like

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)	

and measures like

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
&& 'Date'[Week Rank]>=max('Date'[Week Rank])-3))