covid19 - dax formula for previous weekly and yearly cumulatives/running totals

[Anonymous]

hi,

ref sample file.

i have managed to get the dax working for the previous daily, monthly & quarterly cummulative/running totals. however, i'm having problems to get the same for previously week and yearly cummulative/running totals.

1st i get the sum of newcases with

SUM( 'cv19-data'[New_cases] )

then, for previous day, month, quarter, year calculation, i use the DATEADD function such as

CALCULATE ( [NewCases], DATEADD ( 'Calendar'[Date], -1, DAY ) )
or
CALCULATE ( [NewCases], DATEADD ( 'Calendar'[Date], -1, MONTH ) )
or
CALCULATE ( [NewCases], DATEADD ( 'Calendar'[Date], -1, QUARTER ) )
or
CALCULATE ( [NewCases], DATEADD ( 'Calendar'[Date], -1, YEAR ) )

for previous week, i use

VAR CurrentWeek = SELECTEDVALUE( 'Calendar'[WeekNo] )
 
VAR CurrentYear = SELECTEDVALUE( 'Calendar'[Year] ) 

VAR MaxWeekNo = CALCULATE( MAX ( 'Calendar'[WeekNo] ), ALL ( 'Calendar' ) ) 

RETURN 

SUMX( FILTER( ALL ( 'Calendar' ), 
  IF ( CurrentWeek = 1, 'Calendar'[WeekNo] = MaxWeekNo 
  && 'Calendar'[Year] = CurrentYear -1, 
    'Calendar'[WeekNo] = CurrentWeek -1 
    && 'Calendar'[Year] = CurrentYear ) 
     ), 
  [NewCases] ) 

for the cummulative/running totals calculations, i have no issues with the previous day, previous month, & previous quarter. i need help to find the cummulative/running totals for previous week and previous year.

thanks in advance for any help.

krgds, -nik

[amitchandak · Accepted solution]

@Anonymous , Ideally this formula should work as cumulative for day week and month etc

Cumm Sales = CALCULATE([new case], filter(allselected(Calendar),Calendar[date] <=max(Calendar[Date])))

Use week rank as a new column and use that for week related stuff

new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

measures
This Week = CALCULATE([new cases], FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE([new cases], FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

Cumm Week = CALCULATE([new cases], FILTER(ALL('Date'),'Date'[Week Rank] <=max('Date'[Week Rank])))

[Anonymous]

tks again, @amitchandak.

i hv also managed to find week-over-week calc.

krgds, -nik