Forum Discussion

rsbin's avatar
rsbin
Community Champion
1 year ago
Solved

Week over Week Change

Good Afternoon,

Like many others, I am having issues getting my Week over Week Change to work properly. 
Have watched numerous videos, but have now turned myself into knots.  So reaching out to get myself untangled. 
Just need to get my APBalance_PriorWeek measure working, then I can proceed with %Change.  

Summary of Data Table:

Year-WeekNumMon Latest Date AgeCategory APBalance_Weekly
2025-07 2/16/2025 Current $3,830
2025-07 2/16/2025 30Days $2,672
2025-07 2/16/2025 60Days $1,366
2025-08 2/23/2025 Current $3,288
2025-08 2/23/2025 30Days $2,340
2025-08 2/23/2025 60Days $1,332
2025-09 3/2/2025 Current $3,021
2025-09 3/2/2025 30Days $2,976
2025-09 3/2/2025 60Days $548
2025-10 3/9/2025 Current $2,118
2025-10 3/9/2025 30Days $2,074
2025-10 3/9/2025 60Days $1,323
2025-11 3/12/2025 Current $2,181
2025-11 3/12/2025 30Days $2,032
2025-11 3/12/2025 60Days $1,349

My Measure is:

 

 

APBalance_Weekly = CALCULATE( SUM( APAging[Balance(,000)] ),
                         FILTER( DIM_Date, DIM_Date[Date] = MAX( DIM_Date[Date] )))

 

 

Just as a note my AP Balance file is updated Daily. So for the Current Week ( 2025-11) the balance is as of the latest date.

Year -Week and Latest Date are derived from DIM_Date table.

Thanks in advance and Best Regards,

 

 

Need Help
  • Deku's avatar
    Deku
    1 year ago

    not pretty but should work

    previousWeek = 
var maxdt = MAX( dates[Date] )
var maxdtPerWeek =
    CALCULATETABLE(
        ADDCOLUMNS(
            SUMMARIZE( dates, dates[Week No] )
            ,"@MaxDate", CALCULATE( MAX( dates[Date] ) )
        ),
        REMOVEFILTERS( dates )
        ,dates[Date] <= maxdt
    )
var prevWeek =
	INDEX(
		2,
		maxdtPerWeek
		,ORDERBY( [@MaxDate], desc )
	)
var prevWeekDate =
    SELECTCOLUMNS(
        prevWeek
        ,"@date", [@MaxDate]
    )
RETURN
CALCULATE( 
    SUM( 'Table'[balance] ),
    TREATAS( prevWeekDate, dates[Date] )
)

     

     

     

3 Replies

  • Deku's avatar
    Deku
    Super User
    1 year ago

    not pretty but should work

    previousWeek = 
var maxdt = MAX( dates[Date] )
var maxdtPerWeek =
    CALCULATETABLE(
        ADDCOLUMNS(
            SUMMARIZE( dates, dates[Week No] )
            ,"@MaxDate", CALCULATE( MAX( dates[Date] ) )
        ),
        REMOVEFILTERS( dates )
        ,dates[Date] <= maxdt
    )
var prevWeek =
	INDEX(
		2,
		maxdtPerWeek
		,ORDERBY( [@MaxDate], desc )
	)
var prevWeekDate =
    SELECTCOLUMNS(
        prevWeek
        ,"@date", [@MaxDate]
    )
RETURN
CALCULATE( 
    SUM( 'Table'[balance] ),
    TREATAS( prevWeekDate, dates[Date] )
)

     

     

     

    • rsbin's avatar
      rsbin
      Community Champion
      1 year ago

      Deku ,

      Thanks for the response.  Not sure if I will be able to attend to this today, but will give it a shot when I get the chance.

      Kind Regards,

    • rsbin's avatar
      rsbin
      Community Champion
      1 year ago

      Deku ,

      It appears to be working for me.  Will have to study in more detail to understand it, but it looks to be a solution I was after.

      Thanks again and apologies for the delayed response.