Measure to show sales by person over 7 day period

[Anonymous]

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,

[v-xicai]

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

[v-xicai]

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.

[PaulDBrown]

@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:

[amitchandak]

@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

https://www.youtube.com/watch?v=duMSovyosXE

[Anonymous]

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?

Your code is

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

[Greg_Deckler]

@Anonymous Try:

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