WTD Verus last WTD

tmears ,

I attached the pbix below, but here's an overview of what I did. The date table you were using didnt have the all the dates for 2019. I looked at the code but couldnt see how to fix that. So I created a new date table using power query.  You can look at the applied steps and see what I did. I basically needed a way to have a cumulative week number, year, and the day week #.  I added in the Month Name ( and Month to sort the month name) as well. I then created a datekey to match your datekey in the sales table.  Once loaded I made sure to mark that as date table, and related that to your fact table. 

Here's the M code, but probably easier to step through

let
    Source = Query1(2018, 2019),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
    #"Inserted Week of Year" = Table.AddColumn(#"Renamed Columns", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
    #"Inserted Year" = Table.AddColumn(#"Inserted Week of Year", "Year", each Date.Year([Date]), Int64.Type),
    #"Grouped Rows" = Table.Group(#"Inserted Year", {"Year", "Week of Year"}, {{"Count", each _, type table [Date=date, Week of Year=number, Year=number]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Index",{"Index", "Count"}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Removed Other Columns", "Count", {"Date"}, {"Date"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Count",{"Date", "Index"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"Index", "WeekNum"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns1", "DateKey", each Text.Combine(
    {
        Text.From( Date.Year([Date]) ),

        Text.PadStart(
            Text.From(
                Date.Month([Date])
            ),
            2,
            "0"
        ),

        Text.PadStart(
            Text.From(
                Date.Day([Date])
            ),
            2,
            "0"
        )
    }
)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"DateKey", type text}}),
    #"Inserted Month Name" = Table.AddColumn(#"Changed Type1", "Month Name", each Date.MonthName([Date]), type text),
    #"Inserted Month" = Table.AddColumn(#"Inserted Month Name", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Year1" = Table.AddColumn(#"Inserted Month", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Year1", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type)
in
    #"Inserted Day of Week"

Now that we have a proper date table, I put Year, Week Number and date in a matrix. Then I started with your Count measure.  Next measure was the week to date:

Week to Date = 
--Set Variables
Var __CurrentWeekNum= MAX( NewDateTable[WeekNum]) 
Var __CurrentDate=MAX( NewDateTable[Date])
return

IF( 
    COUNTROWS(FactSalesHistory)>0,                           --Prevents figures from being projected into future dates
        CALCULATE(
            [COunt of Sales History ID1],
                Filter(
                 ALL( NewDateTable),                        --Remove all the filters from the NewDate Table
                  __CurrentWeekNum = NewDateTable[WeekNum]  --Then only want rows where the weeknumnber = the current weeknumber in the current filter context
                  &&
                  __CurrentDate >= NewDateTable[Date]       --And where the current date is >= the date. So that we get a list of dates for the running total
                )
            )
        )

Prev Week To Date = 
--Set Variables for easier code
VAR __CurrentWeek = Max( NewDateTable[WeekNum])
Var __PrevWeek = __CurrentWeek - 1
Var __CurrentDay=max( NewDateTable[Day of Week])
RETURN

IF( 
    COUNTROWS(FactSalesHistory) >0,
        CALCULATE(
            [COunt of Sales History ID1],
        FILTER(
            ALL( NewDateTable),
            NewDateTable[WeekNum] = __PrevWeek          --Now want all the rows in the date table that equal the previous weeknumber
            &&
            __CurrentDay >=NewDateTable[Day of Week]    --Then only want those day numbers that are equal or > the current day number     
            
        )
    )
)