Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
Framet
Resolver II
Resolver II

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

View solution in original post

12 REPLIES 12
Framet
Resolver II
Resolver II

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
Not applicable

Hi,

 

Is the best way to go with an new date table? I know that DAX formules often refers to this technique. But is looks circuitous

Greg_Deckler
Super User
Super User

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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Hi,

 

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

Because the first formula combines year en week 

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

 

 

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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#")

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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. 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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
Not applicable

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?

 

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.


Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.