Forum Discussion

alexjaco's avatar
alexjaco
Frequent Visitor
6 years ago

Previous Week Calculation

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!
 
Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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!