Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Measure KPI for this week versus last week

Hi,

I want to measure KPI for this week versus last week with the following code but its not working for last week. Please help.

 

Current_Week =
Var _select = SELECTEDVALUE('S&R and PE data'[Week Ending])
Return
CALCULATE(Sum('S&R and PE data'[Referral Count]),FILTER('S&R and PE data','S&R and PE data'[Weeknum]=WEEKNUM(_select)))
 
Last_Week =
Var _select = SELECTEDVALUE('S&R and PE data'[Week Ending])
Return
CALCULATE(Sum('S&R and PE data'[Referral Count]),FILTER('S&R and PE data','S&R and PE data'[Weeknum]= WEEKNUM(_select)-1))
 
The total referral count this week is calculated correctly (which is 1226) but the code gives me blank for last week. Please help correct the code!
 
Sample data: 

Week Ending Referral Count WeekNum

07/07/23        1                      27

07/07/23        1                      27

14/07/23        1                      28

14/07/23         1                      28

21/07/23        1                       29

Need Help

8 Replies

  • sergej_og's avatar
    sergej_og
    Icon for Super User rankSuper User
    3 years ago

    Just an idea...what about using OFFSET function?
    I had a similar case last week. Solved with OFFSET function.
    Instead of your FILTER-Part try OFFSET function.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi, 

      Offset is also giving me "Blank" in Last week's measure. 

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi, Anonymous 

     

    You can try the following methods.

    Current_Week = 
Var _select = MAX('S&R and PE data'[Week Ending])
Return
CALCULATE(Sum('S&R and PE data'[Referral Count]),FILTER(ALL('S&R and PE data'),'S&R and PE data'[Weeknum]=WEEKNUM(_select)))
    Last_Week = 
Var _select = MAX('S&R and PE data'[Week Ending])
Return
CALCULATE(Sum('S&R and PE data'[Referral Count]),FILTER(ALL('S&R and PE data'),'S&R and PE data'[Weeknum]= WEEKNUM(_select)-1))

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi, 

      I expect to see 

      Current Week Referral count Last Week Referrals Count

      1226                                       1109

      With the formula i only get 1226 not 1109

       

      I am trying to show a %WOW over change using KPI visual with has Current Week in Value, Trend Axis has Referral Count and Last week in Target. 

       

      The last week measure isn't calculating the sum of referrals done over last week. 

       

      • v-zhangti's avatar
        v-zhangti
        Icon for Community Support rankCommunity Support
        3 years ago

        Hi, Anonymous 

         

        Can you provide more example data?

         

        Best Regards