Previous Week Calculation

[alexjaco]

Hi, 

I am trying to compare each weeks collection amounts to the previous week. I have the following formula:

Last weeks Collections Measure =

VAR CurrentWeek = SELECTEDVALUE(Collections[Week Number])

Var CurrentYear = SELECTEDVALUE(Collections[Year])

Var MaxWeekNumber = CALCULATE(MAX(Collections[Week Number]),ALL(Collections))

Return

CALCULATE(SUM(Collections[Weekly Collections]),

Filter(ALL(Collections),

IF(CurrentWeek=1,

Collections[Week Number] = MaxWeekNumber && Collections[Year]=CurrentYear-1,

Collections[Week Number] = CurrentWeek-1 && Collections[Year]=CurrentYear)))

When viewing as a table at the date level it works. See below:

However, when other categories are added in, the DAX formula stays filtered at the date level. See below for an example. I've tried swithcing the "CALCULATE" to a "SUMX" but I get the same results. Thank you in advance for your help!

[amitchandak]

@alexjaco , refer my blog on Week/WTD. I have used week rank to get this done

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

[Anonymous]

Hi @alexjaco ,

Try the measure as below:

Last weeks Collections Measure = 
VAR CurrentWeek = SELECTEDVALUE(Collections[Week Number])
Var CurrentYear = SELECTEDVALUE(Collections[Year])
Var MaxWeekNumber = CALCULATE(MAX(Collections[Week Number]),ALL(Collections))
Return
CALCULATE(SUM(Collections[Weekly Collections]),
Filter(ALL(Collections),[State]=MAX([State])&&
IF(CurrentWeek=1,
Collections[Week Number] = MaxWeekNumber && Collections[Year]=CurrentYear-1,
Collections[Week Number] = CurrentWeek-1 && Collections[Year]=CurrentYear)))

If the above doesnt work,pls upload your .pbix file to onedrive business and share the link with us.Do remember to remove the confidential information.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!