Forum Discussion

Aaron_Saunders's avatar
Aaron_Saunders
New Member
1 year ago
Solved

Capacity and schedule expressions

How would i express single counts through a date range as averages per week over the course of the project?

I have a single count of the total number of items to be created, a start date for the project and an end date for the project. I want a bar graph that will take those single projects and for each week starting on Sunday express the total number of items to be created that week. for the blue project that has 1132 units demonstrated on the week of Oct 17th ending the week of Dec 19th, i want it to express as 114 units over the course of the 10 weeks that it occurs. then also for all the other projects to express the same way. I'm lost on this one! The data source is an append of two separate smartsheets that house all the infomration, and the unit count is a calculated column added in the append data source as a calculated column. I can modify the origin sources via power query, can modify the append via power query, or can add columns in the excel view. I can alter the original data sources, but i would prefer to leave those alone.

 

Need Help
  • Kedar_Pande's avatar
    Kedar_Pande
    1 year ago

    Create a Date Table:

    DateTable = CALENDAR(MIN('YourTable'[Start Date]), MAX('YourTable'[End Date]))

    Add a Week Start Column:

    WeekStart = 'DateTable'[Date] - WEEKDAY('DateTable'[Date], 1) + 1

    Create a Weekly Average Measure:

    WeeklyAverage = 
    DIVIDE(
    SUM('YourTable'[Total Count]),
    COUNTROWS(FILTER('DateTable', 'DateTable'[Date] >= MIN('YourTable'[Start Date]) && 'DateTable'[Date] <= MAX('YourTable'[End Date])))
    )

    Create Bar Chart:

    X-axis: WeekStart
    Values: WeeklyAverage

     

    If this helped, a Kudos 👍 or Solution mark would be great!🎉
    Cheers,
    Kedar Pande
    Connect on LinkedIn

1 Reply

  • Kedar_Pande's avatar
    Kedar_Pande
    Icon for Super User rankSuper User
    1 year ago

    Create a Date Table:

    DateTable = CALENDAR(MIN('YourTable'[Start Date]), MAX('YourTable'[End Date]))

    Add a Week Start Column:

    WeekStart = 'DateTable'[Date] - WEEKDAY('DateTable'[Date], 1) + 1

    Create a Weekly Average Measure:

    WeeklyAverage = 
    DIVIDE(
    SUM('YourTable'[Total Count]),
    COUNTROWS(FILTER('DateTable', 'DateTable'[Date] >= MIN('YourTable'[Start Date]) && 'DateTable'[Date] <= MAX('YourTable'[End Date])))
    )

    Create Bar Chart:

    X-axis: WeekStart
    Values: WeeklyAverage

     

    If this helped, a Kudos 👍 or Solution mark would be great!🎉
    Cheers,
    Kedar Pande
    Connect on LinkedIn