Forum Discussion
count this week
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
Hi Greg_Deckler
Your solution is perfect for me but I can't figure out how to add the leading 0's for the week numbers early in the year! Can you tell me how I work this into the columns I have created?
I have:
First Reg Week = YEAR([First Reg]) & WEEKNUM([First Reg])
This week = YEAR(NOW())& WEEKNUM(NOW())
Week diff = [First Reg Week] - [This week]
Thanks in advance!
Heather
You want to wrap your WEEKNUM functions in a FORMAT like this:
FORMAT([WeekNum],"0#")
So, in your case for the first one it would be like:
First Reg Week = YEAR([First Reg]) & FORMAT(WEEKNUM([First Reg]),"0#")
Thank you - this gives me an error though: Cannot convert value of type Text to type Integer.
Is there a way to do this but keep it as a whole number column? Sorry if this is basic!
H
Hmm, I can't figure out how you are getting that.