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

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?

 

Framet's avatar
Framet
Resolver II
9 years ago

I can only apologise as I'm not fully grasping what you need. 

 

Is it the difference between the number of orders recieved this week against the previous week?

 

Either way, in answer to your other question. If you have a date column it can help to create a date table and link the two. The main reason being creating measures with time inteligence is much easier such as This Week, Prior Week, Last Year, Year to Date etc.

 

The full list of time intelligence functions is here:
https://msdn.microsoft.com/en-us/library/ee634763.aspx

 

I can only relate to my personal experience, in which I always create a calendar table in any data model with dates.