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 ...
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.

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??