YTD for Weeks

Hi Anonymous - Make sure your date table has weeknumber in it as like below:

You can create a YTD measure that considers the week level by using TOTALYTD:

YTD Sales =
CALCULATE(
SUM(Saless[sales_amount]),
FILTER(
ALL(DateTable),
DateTable[Year] = MAX(DateTable[Year]) &&
DateTable[Date] <= MAX(DateTable[Date])
)
)

or specifically handle YTD up to the end of the current week:

YTD Sales by Week =
CALCULATE(
SUM(Saless[sales_amount]),
FILTER(
ALL(DateTable),
DateTable[Year] = MAX(DateTable[Year]) &&
DateTable[Week Number] <= MAX(DateTable[Week Number])
)
)

Hope it works.

Hi Anonymous, there are 2 reasons the reason why you can't apply YTD to weeks:

1. Weeks are not uniquely assgined to year/quarter/month (for instance week 52 of 2021 included the preiod 27th of Dec 2021 - 2nd Jan 2022). So where do you want to see it in a hierarchy? Under 2021 or 2022?
2. Same week corresponds to different period in 2 consequent years, therefore comparison Year over Year is complex as well (do you want to compare the same calendar period or the same week number?)

Once you answer questions above for yourself you can use Calculate() function to ovewrite filters.

The following DAX code for Calendar Table will help you. Feel free to adapt it to your needs. The column you're interested in is "Year-Week" (you can copy-paste this code and create a new table in PowerBI to play around).

c_Calendar = 
VAR MinDate = DATE( 2020,1,1)
VAR MaxDate = DATE( 2025,12,31)

VAR BaseCalendar =
    CALENDAR( MinDate, MaxDate )

RETURN
    GENERATE(
        BaseCalendar,
        VAR _BaseDate = [Date]
        VAR _YearDate = YEAR( _BaseDate )
        VAR _MonthNumber = MONTH( _BaseDate )
        VAR _WeekNum = WEEKNUM( _BaseDate, 21 )                                          //ISO format is used to properly get the correct week number for the end of the year 
        
        RETURN ROW (
            "Year", _YearDate,
            "Month", _MonthNumber,
            "Month Name", FORMAT( _BaseDate, "MMM" ),
            "Week", _WeekNum,
            "Year-Week",
                IF(
                    _WeekNum > 50 && _MonthNumber = 1,                                   //January doesn't have weeks more than 5, so if WeekNum is > 50, it's the end of the previous year
                    INT( _YearDate-1 & FORMAT( _WeekNum, "00" ) ),                       //therefore ISO Year corresponds to year-1
                    IF(
                        _WeekNum < 3 && _MonthNumber = 12,                               //the same for Dec and week less than 3, which can't be in Dec, so if WeekNum is < 3, it's the beginning of the next year
                        INT( _YearDate+1 & FORMAT( _WeekNum, "00" ) ),                   //therefore ISO Year corresponds to year+1
                        INT( _YearDate & FORMAT( _WeekNum, "00" ) )
                    )
                ),                           
            "Year-Month Number", INT( _YearDate & FORMAT( _MonthNumber, "00" ) ), 
            "Month-Year", FORMAT( _BaseDate, "MMM" ) & "-" & _YearDate )
    )

Good luck with your project!