Previous Week Figures Using Calendar Table

[BryanWhellams]

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.

 

[Ashish_Mathur]

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?

[v-qiuyu-msft · Accepted solution]

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