Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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...
  • Framet's avatar
    Framet
    9 years ago

    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

Greg_Deckler's avatar
Greg_Deckler
Community Champion
9 years ago

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#")
Anonymous's avatar
Anonymous
Not applicable
9 years ago

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

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    Hmm, I can't figure out how you are getting that.