Forum Discussion

BryanWhellams's avatar
BryanWhellams
Frequent Visitor
8 years ago
Solved

Previous Week Figures Using Calendar Table

Hi all

 

I am trying to get the previous weeks figures based on the week in a calendar table.

 

Specifically I want to be able to create a table that uses the dates from the calendar table to get appointment figures for the current week and the previous week. 

 

Please see tables below.

 

Any help greatly appreciated.

 

 

Need Help
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    8 years ago

    Hi BryanWhellams,

     

    You can create measures below: 

     

    CurrentWeekTotal = CALCULATE(SUM(Table1[Appointments]),FILTER(ALL(Table1),'Table1'[WeekNumber]=MAX('Calendar'[Weeknum])))

     

    PreviousWeekTotal = CALCULATE(SUM(Table1[Appointments]),FILTER(ALL(Table1),'Table1'[WeekNumber]=MAX('Calendar'[Weeknum])-1))

     

     

    For details, you can download attached pbix file. 

     

    Best Regards,
    Qiuyun Yu 

2 Replies

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    8 years ago

    Hi BryanWhellams,

     

    You can create measures below: 

     

    CurrentWeekTotal = CALCULATE(SUM(Table1[Appointments]),FILTER(ALL(Table1),'Table1'[WeekNumber]=MAX('Calendar'[Weeknum])))

     

    PreviousWeekTotal = CALCULATE(SUM(Table1[Appointments]),FILTER(ALL(Table1),'Table1'[WeekNumber]=MAX('Calendar'[Weeknum])-1))

     

     

    For details, you can download attached pbix file. 

     

    Best Regards,
    Qiuyun Yu 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi BryanWhellams,

     

    Is there a possibility to not have data for a certain week(s).  So, you may have data for 1701, 1702 and 1704.  There is no data for 1703.  Is this possible?