Solved: Previous Week Figures Using Calendar Table

BryanWhellams · ‎10-17-2017

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.

v-qiuyu-msft · ‎10-17-2017

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Ashish_Mathur · ‎10-17-2017

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?

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

v-qiuyu-msft · ‎10-17-2017

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.