Sales by rolling 7 day rather than by week

[Anonymous]

Hi, I have a calculated column that returns sales for a week by area with following syntax:

Sales by Area by Week = CALCULATE(count(Sales[Area]), ALLEXCEPT(Sales, Sales[Area],Sales[Week]))

I can then calculate sales per week per population using column:

Sales Rate = 'Sales'[Sales by Area by Week]/'Sales'[Population]

How can I amend this first calculated column please to provide a rolling 7 day figure that changes once per day rather than once per week? I have a sales date column with the Sales table.

Kind regards

[aj1973]

Hi,

Do you have a column Sales[Day] in your Model? if not you need to create a Calendar Table.

[Anonymous]

Hi thanks, yes I do have a Sales[Day] column in the table and a separate calendar table and there is a relationship between the two. 

[aj1973]

So you looking to calculate the Running total!

You Either use the quick measure feature in the desktop or use this formula

running total in Date =

CALCULATE(

    count(Sales[Area]),

    FILTER(

        ALLSELECTED('Calender'[Date]),

        ISONORAFTER('Calender'[Date], MAX('Calender'[Date]), DESC)

    )

)

[Anonymous]

Thanks that is great but is needs to be a grouped total of the last 7 days that changes each day, i.e. on 8th November = all sales for dates 2nd - 8th November. On 9th November = all sales for dates 3rd-9th November. Can you help with that please? 

Many thanks 

[aj1973]

Not sure I understand what you need! But add "-1" to the formula if that's what you are looking for

running total in Date Yesterday =

CALCULATE(

    count(Sales[Area]),

    FILTER(

        ALLSELECTED('Calender'[Date]),

        ISONORAFTER('Calender'[Date], MAX('Calender'[Date])-1, DESC)

    )

)

or maybe you are looking for some thing like this : the difference between the 2 formulas i recomended

 

Diff = [running total in Date]-[running total in Date Yesterday]

[Anonymous]

Thanks, I appreciate your help but what I am looking for is a measure that can be plotted on a time-based chart showing 7 day sales over a rolling period. With my original code I can plot sales by week:

Sales by Area by Week = CALCULATE(count(Sales[Area]), ALLEXCEPT(Sales, Sales[Area],Sales[Week]))

But what I would instead like to be able to do is to plot the rolling number of sales by 7 day period, so for example it might be

2nd - 7th November = 10 sales

3rd - 8th November = 15 sales

4th - 9th November = 20 sales

Then on the chart, have 7th, 8th and 9th November represented by values of 10, 15 and 20.

Hope this makes sense. 

[aj1973]

Well the formulas i proposed do the exact that, But to have 7 days difference in your running total just subtract 7 days instead of 1 day in the 

running total in Date N Days =

CALCULATE(

    count(Sales[Area]),

    FILTER(

        ALLSELECTED('Calender'[Date]),

        ISONORAFTER('Calender'[Date], MAX('Calender'[Date])-N days, DESC)

    )

)

Then calculate the Difference here Diff = [running total in Date]-[running total in Date N days]

Hope it works, or maybe if you share a Dummy PBIX to help you more.