cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Measure to show sales by person over 7 day period

Hi all,

I have put together the below measure which gives a sum of all sales within my organisation over the past 7 days.

7 Days Sales = CALCULATE(COUNT('Sales'[IDNumber]), FILTER(All('Sales'), 'Sales'[Sales Date] >= TODAY()-7))

This gives me the collective number of 120 sales over a rolling 7 day period. However, we have 5 different salespeople (column 'Salesperson'). How do I use PowerBI to display the number of sales within this date period by Salesperson, is another measure required?

Thanks so much,
6 REPLIES 6
Community Support

Hi   @Anonymous ,

Do the suggestions from engineers make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

Community Support

Hi @Anonymous ,

You may change your measure like DAX below.

``````7 Days Sales =
CALCULATE (
COUNT ( 'Sales'[IDNumber] ),
FILTER (
ALLEXCEPT ( 'Sales', [Salesperson] ),
'Sales'[Sales Date]
>= TODAY () - 7
)
)
``````

Best Regards,

Amy

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User

@Anonymous

Just on a side note, if you want the 7 days calculation, you should be using 'FILTER(All('Sales'), 'Sales'[Sales Date] >= TODAY()-6)) in your expressions:

Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Super User

@Anonymous , You can have it like this with a date table

Rolling 7 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-7,Day))

Rolling 7 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-7,Day))

But this sometimes can group data into one bucket. You can check this solution for that

Anonymous
Not applicable

Hi, thanks so much for this. Can you advise please how the code could be amended so that it returned sales between 2 and 9 days ago?

Rolling 7 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-7,Day))

Changing the 7 to a 9 is easy enough but not sure how to work with the MAX part to make it 2 days ago on a rolling basis?

Thank you

Super User

@Anonymous Try:

``7 Days Sales = CALCULATE(COUNT('Sales'[IDNumber]), FILTER(ALLSELECTED('Sales'), 'Sales'[Sales Date] >= TODAY()-7))``

@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors