Week on Week Comparison

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.