Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How to setup KPI for previous week and previous 2 weeks

Hello,

 

I have the following table from Excel and imported into Power BI. The ISO week number is calculated from the Date. I would like to have a KPI card that dynamically shows the previous week total submissions against the previous 2 weeks submissions. So, we are currently in ISO week number 7 and the KPI visualization should show the total submissions in week 6 against week 5 (as reference). I don't know how to do this. Is this possible? Any help is much appreciated!

 

DateWeek NumberStudent NameSubmissions
03/01/20221Lizui4
06/01/20221Laufenburg7
11/01/20222Tegalpapak8
12/01/20222Ar Rabiyah5
22/01/20223Bellegarde3
21/01/20223Gangarampur3
25/01/20224Luntas1
26/01/20224Frei Paulo6
26/01/20224Seedorf2
03/02/20225Bellegarde3
05/02/20225Gangarampur3
11/02/20226Luntas1
09/02/20226Cosamaloapan de Carpio7
15/02/20227Zagrodno9

 

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi Anonymous 

     

    You can use these measures.

    Previous Week Total = 
var _weekEnd = TODAY() - WEEKDAY(TODAY(),2)
var _weekStart = _weekEnd - 6
return
CALCULATE(SUM('Table'[Submissions]),ALL('Table'),'Table'[Date]>=_weekStart,'Table'[Date]<=_weekEnd)
    Previous 2 Week Total = 
var _weekEnd = TODAY() - WEEKDAY(TODAY(),2) - 7
var _weekStart = _weekEnd - 6 - 7
return
CALCULATE(SUM('Table'[Submissions]),ALL('Table'),'Table'[Date]>=_weekStart,'Table'[Date]<=_weekEnd)

     

    I don't use the week number column. I calculate the week start date and week end date based on today's date directly in measures and use them to filter the table. My week is from Monday to Sunday. You can adjust the number substracted if needed. 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

5 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous 

     

    You can use these measures.

    Previous Week Total = 
var _weekEnd = TODAY() - WEEKDAY(TODAY(),2)
var _weekStart = _weekEnd - 6
return
CALCULATE(SUM('Table'[Submissions]),ALL('Table'),'Table'[Date]>=_weekStart,'Table'[Date]<=_weekEnd)
    Previous 2 Week Total = 
var _weekEnd = TODAY() - WEEKDAY(TODAY(),2) - 7
var _weekStart = _weekEnd - 6 - 7
return
CALCULATE(SUM('Table'[Submissions]),ALL('Table'),'Table'[Date]>=_weekStart,'Table'[Date]<=_weekEnd)

     

    I don't use the week number column. I calculate the week start date and week end date based on today's date directly in measures and use them to filter the table. My week is from Monday to Sunday. You can adjust the number substracted if needed. 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      v-jingzhang Thanks a lot for the support! I have a follow-up question - if i add an extra column called "Feedback", how to sum and group the number values with the "Submissions" column, according to the dates? That is, i want to show the total number of submissions and feedback in the previous week and previous 2 weeks. Is it possible? Any help is much appreciated!

       

      DateWeek NumberStudent NameSubmissionsFeedback
      03/01/20221Lizui42
      06/01/20221Laufenburg75
      11/01/20222Tegalpapak88
      12/01/20222Ar Rabiyah56
      22/01/20223Bellegarde38
      21/01/20223Gangarampur33
      25/01/20224Luntas11
      26/01/20224Frei Paulo65
      26/01/20224Seedorf21
      03/02/20225Bellegarde34
      05/02/20225Gangarampur37
      11/02/20226Luntas12
      09/02/20226Cosamaloapan de Carpio73
      15/02/20227Zagrodno99
      • v-jingzhang's avatar
        v-jingzhang
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi Anonymous 

         

        Sorry it's not clear to me. Can you show the new expected result based on your sample data? I guess you may want something like below？

        Previous Week Total = 
var _weekEnd = TODAY() - WEEKDAY(TODAY(),2)
var _weekStart = _weekEnd - 6
return
CALCULATE(SUM('Table'[Submissions])+SUM('Table'[Feedback]),ALL('Table'),'Table'[Date]>=_weekStart,'Table'[Date]<=_weekEnd)
        Previous 2 Week Total = 
var _weekEnd = TODAY() - WEEKDAY(TODAY(),2) - 7
var _weekStart = _weekEnd - 6 - 7
return
CALCULATE(SUM('Table'[Submissions])+SUM('Table'[Feedback]),ALL('Table'),'Table'[Date]>=_weekStart,'Table'[Date]<=_weekEnd)

         

        Best Regards,
        Community Support Team _ Jing
        If this post helps, please Accept it as Solution to help other members find it.

  • mh2587's avatar
    mh2587
    Icon for Super User rankSuper User
    4 years ago
    Last 2 Week Submission = CALCULATE(SUM(Table[Submissions]),DATEADD('Table'[Date],-14,DAY))
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi mh2587 Thanks for your reply! But the problem is that it is 14 days behind, am i correct? I want to isolate each week completely, the whole of week 6 against week 5 (target) and it should not matter which day of the current week (number 7) that i refresh this data to recalculate. So, i think maybe it is more practical to consider by week number instead? So, here is the simplified table below:

       

      Week NumberStudent NameSubmissions
      1Lizui4
      1Laufenburg7
      2Tegalpapak8
      2Ar Rabiyah5
      3Bellegarde3
      3Gangarampur3
      4Luntas1
      4Frei Paulo6
      4Seedorf2
      5Bellegarde3
      5Gangarampur3
      6Luntas1
      6Cosamaloapan de Carpio7
      7Zagrodno9


      The problem is that if today is wednesday, then it will consider the last 14 days so not the last 2 weeks entirely, since it will only consider part of week 5 and then also part of current week 7. How to fix this? Maybe work only with the week numbers instead of the date?

      Also, i don't understand how to use this line of code in the 3 fields for the KPI visualization??