Dax : 2019 Weekly Year to Date

[AdrianneNokes]

I am trying to get a formula to work so it will automatically update as new weekly data comes in. 

With these formulas I can get it to work

2022 YTD = SumX(CALCULATETABLE('Transactions Period','Transactions Period'[Year]=2022),'Transactions Period'[Transactions.Transactions])

CYMAXWK = MAXX(FILTER('Transactions Period','Transactions Period'[Year]=2022),'Transactions Period'[Week])

2019 YTD = SumX(FILTER(CALCULATETABLE('Transactions Period'),'Transactions Period'[Week]<=19&&'Transactions Period'[Year]=2019),'Transactions Period'[Transactions.Transactions])

When I switch the 2019 YTD formula to 

2019 YTD = SumX(FILTER(CALCULATETABLE('Transactions Period'),'Transactions Period'[Week]<='Transactions Period'[CYMAXWK]&&'Transactions Period'[Year]=2019),'Transactions Period'[Transactions.Transactions])

My results show this

IHow can I work around this so I can keep comparing same weeks YTD to previous years. I am getting confused. 

[v-yangliu-msft · Accepted solution]

Hi  @AdrianneNokes ,

I created some data:

Here are the steps you can follow：

1. Create measure.

CYMAXWK =
MAXX(
   FILTER( ALL('Table'),'Table'[Date]=MAXX(ALL('Table'),'Table'[Date])),WEEKNUM('Table'[Date],1))

2019 YTD =
SUMX(
FILTER(ALL('Table'),'Table'[Date]>=DATE(2019,1,1)&&'Table'[Date]<=MAXX(FILTER(ALL('Table'),WEEKNUM('Table'[Date],1)=[CYMAXWK]&&YEAR('Table'[Date])=2019),[Date])),[rand])

2022 YTD =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Date]>=DATE(YEAR(TODAY()),1,1)&&
    'Table'[Date]<=TODAY()
    ),[rand])

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[tom480]

Hi @AdrianneNokes ,

Are you using a marked date table in your model?  I mocked up some data with a standard date table and used sameperiodlastyear with a slicer and getting the results I expected.

If this is helpful, please mark as a solution so others can find it too!  Thanks, Tom 😀

Date Table (DAX)

TableDT =

VAR MinYear = YEAR ( MIN ( Table1[Date] ) )

VAR MaxYear = YEAR ( MAX ( Table1[Date] ) )

RETURN

ADDCOLUMNS (

FILTER (

CALENDARAUTO( ),

AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )

),

"Month Name", FORMAT ( [Date], "mmmm" ),

"Month Number", MONTH ( [Date] ),

"Day", DAY( [Date] ),

"DOW", FORMAT ( [Date], "Ddd" ),

"Week Number", WEEKNUM( [Date] ),

"Week Day", WEEKDAY( [Date] ),

"Quarter", QUARTER( [Date] ),

"Year", YEAR ( [Date] )

)

Simple Date TableMatrix, cards, slicer by year