OK.
I’m not sure to understand properly: you don’t care about which week it is, just weekday ?
If so, you may want to create a table with just the weekday, and set a relation between that table and the one you have at the moment.
My suggestion:
-
table “EggCollection” (the one you have at the moment), with columns:
==> key: type Text, initial value expression UNIQUEID()
==> timeStamp: type DateTime, initial value expression NOW()
==> quantity: type Number
-
Slice “eggCollectionFromThisWeek”, that will have the last week’s rows only
==> source table EggCollection
==> row filter condition : [TimeStamp]>NOW()-7
-
Table “Week”, with columns:
==> keys: 1, 2 ,3, 4, 5 ,6, 7
==> Values: “sunday”, “monday”, “tuesday”, etc
-
Back on table “EggCollection”, add a virtual column :
- “weekday”, expression: WEEKDAY([TimeStamp]), Type Ref, Source table: Week
- Back on Table “Week”, add virtual* columns:
- “SumEggsCollected”, expression :
SUM([Related eggCollectionFromThisWeek][Quantity])
- “AccumulatedEggsCollected”, expression:
SUM(
SELECT(eggCollectionFromThisWeek[SumEggsCollected],
[WeekDay]<=[_THISROW].[WeekDay]
)
)
Please note:
- I didn’t try this, this is a suggestion
- this should work, with limitation:
WEEKDAY() expression takes Sunday as day 1. You mway want to use SWITCH() expression as an alternative in order to take monday as day 1.
Note2:
Example for the alternative WEEKDAY() expression:
SWITCH(WEEKDAY([TimeStamp]),
1,7,
2,1,
3,2,
4,3
5,4
6,5,
6
)
For reference:
LinkedIn
Twitter
