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

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi,

     

    thanks for your replay. Unfortunately this doesn't work.

    Because the first formula combines year en week 

    • year&weeknum => 20169
    • year&weeknum => 201610 

     

     

    • Framet's avatar
      Framet
      Resolver II
      9 years ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        hi

         

        No the date column has a normal date format.

        But if you make the formule year & weeknumber

        • 2016 & 9 you've got 20169
        • 2016 & 10 you've got 201610

        this gives a wrong answer when i calculate the difference with this week

         

        Is your opinion that i have to look the best solution in the new date table?

         

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      9 years ago

      Oh, yeah, you'll have to get a leading zero in there.

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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