Forum Discussion
Cumulative total (last week)
Hello sevenhills I had a chance to play around with the formula above and was able to make minor tweaks to get what I needed and correct the issue that was happening with your measure. The issue was that the very last cumulative date in the previous week column was matching with the actual previous week's total as I mentioned in a previous post. I've changed it to this, which now captures my last row correctly according to what I need. Thanks for getting me on the right path! Will mark your answer as a solution (with minor tweaks) 😀
Prev Cumulative Week RMA Count =VAR CurrentWeek = MAX('Date Table'[WeekEnding])RETURN CALCULATE([Distinct Count of RMAs], ALLEXCEPT('Date Table','Date Table'[WeekEnding]),'Date Table'[WeekEnding]<= (CurrentWeek - 1) )
CurrentDate is simply the current date. Yes, correct, I need the value "up to" the prior calendar week.
I see you are using year and week number. May I suggest one more way to achieve the same and better
https://forum.enterprisedna.co/t/previous-weeks-calculation/3559/2
Please tune the measure to your needs based on this link.
I will try your measure as follows: Adjust <= or =, as I dont know the [Distinct Count of RMAs]
Previous Cumulative RMA Count =
VAR Currentweek =
SELECTEDVALUE ( 'Date Table'[Week Number] )
VAR CurrentYear =
SELECTEDVALUE ( 'Date Table'[Year] )
VAR MAXweeknumber =
CALCULATE ( MAX ( 'Date Table'[Week Number] ), ALL ( 'Date Table' ) )
RETURN
SUMX (
FILTER (
ALL ( 'Date Table' ),
IF (
Currentweek = 1,
'Date Table'[Week Number] <= MAXweeknumber
&& 'Date Table'[Year] = CurrentYear - 1,
'Date Table'[Week Number] <= Currentweek - 1
&& 'Date Table'[Year] = CurrentYear
)
),
[Distinct Count of RMAs]
)