Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Create a Calculated Column to Sum Weekend Values

I have a date table that has calculated column counting all requests submitted on a given day.

There are two KPIs that need to be tracked.  The first KPI is that each weekday has a capacity for 10 new requests.

This I can track quite easily.

 

The other KPI is that wekends have a capacity of 5 new requests in total over the weekend.

How can I create a column that sums the number of requests made on a Saturday and Sunday of each week?

 

My table currently looks like this:

I want a column that sums 20/08 & 21/08 and places the value in 21/08 so that I can display it as a time series visual

e.g.

Date        Weekend Requests

21/08/22             8

28/08/22             6 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    I think I have the answer.  I used a variable to get teh value of the previous row...

    Weekend Requests =
    var PrevDay = CALCULATE(
        SUM('Calendar'[Date on D2A]),
        DATEADD('Calendar'[Date],-1,DAY)
    )
    return
    IF('Calendar'[DayOfWeekNumber] = 7,
    'Calendar'[Date on D2A] + PrevDay
    )

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    I think I have the answer.  I used a variable to get teh value of the previous row...

    Weekend Requests =
    var PrevDay = CALCULATE(
        SUM('Calendar'[Date on D2A]),
        DATEADD('Calendar'[Date],-1,DAY)
    )
    return
    IF('Calendar'[DayOfWeekNumber] = 7,
    'Calendar'[Date on D2A] + PrevDay
    )

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Shaurya 

     

    Thanks for the reply.  This goes part way.  The total is placed in Day 7 but the final sum places the total for the whole column in the cell and not just the total of the 2 days of that week's day 6&7.