Forum Discussion
count this week
Hi,
maybe a simple question, maybe not
I have a list of orders with a data column. Now i want to count how many orders are made in the last week.
At this moment a made a calculated column so i know the order period
IF(YEAR([orderdate])&WEEKNUM([orderdate])=YEAR(NOW())&WEEKNUM(NOW())+0;"this week";"other week"))
After that i can filter the report on "this week", but i think this is not the way to go.
Does anyone have a good idea to make the measure?
With kind regards, Norbertus
Assuming you don't have a calendar table and at this point you only have dates and not time to worry about you could try:
CountOrdersThisWeek :=
CALCULATE (
COUNTROWS ( 'OrdersTable' ),
FILTER (
'OrdersTable'[orderdate],
[orderdate] <= NOW ()
&& [orderdate]
>= TODAY () - 7
)
)
I would try and avoid anything in a calculated column that refers to NOW() or TODAY() simply because it is only evaluated when the model is loaded (data is refreshed) and subsequently could be incorrect if your data wasn't updated daily and or failed for some reason.Hope this helps some.
Thomas
12 Replies
I would probably do something like:
OrderWeek = YEAR([orderdate])&WEEKNUM([orderdate]) ThisWeek = YEAR(NOW())&WEEKNUM(NOW()) WeekDiff = [OrderWeek] - [ThisWeek]
Now, WeekDiff is 0 for this week, 1 for last week, 2 for 2 weeks ago, etc.
Hi just to help solve this and make it a bit clearer.
Are you saying the [OrderDate] column contains values in the format year&weeknum so 20169 etc?
If it does you could simplfy the prevous measure to:
CountOrdersThisWeek :=
CALCULATE (
COUNTROWS ( 'OrdersTable' ),
FILTER ( [orderdate], [orderdate] = FORMAT ( TODAY (), "YYYYWW" ) )
)
Note that FORMAT ( TODAY (), "YYYYWW" ) is probebly the easiest way to get Year and Wk number if you aren't worried about the week being preceed by a 0 if it is less than 10.
Assuming you don't have a calendar table and at this point you only have dates and not time to worry about you could try:
CountOrdersThisWeek :=
CALCULATE (
COUNTROWS ( 'OrdersTable' ),
FILTER (
'OrdersTable'[orderdate],
[orderdate] <= NOW ()
&& [orderdate]
>= TODAY () - 7
)
)
I would try and avoid anything in a calculated column that refers to NOW() or TODAY() simply because it is only evaluated when the model is loaded (data is refreshed) and subsequently could be incorrect if your data wasn't updated daily and or failed for some reason.Hope this helps some.
Thomas
