cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
paris
Helper IV
Helper IV

Week on Week Comparison

 

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.

 

PowerBIWeekWeek.JPG

 

All the best, 

Paris

1 ACCEPTED SOLUTION
edhans
Super User III
Super User III

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

edhans_0-1606882365115.png

Or by day:

edhans_0-1606883100578.png

 

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.

 

 

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

4 REPLIES 4
edhans
Super User III
Super User III

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!



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
edhans
Super User III
Super User III

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

edhans_0-1606882365115.png

Or by day:

edhans_0-1606883100578.png

 

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.

 

 

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Thank you very much! 

amitchandak
Super User IV
Super User IV

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


Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors