Calculate Total for the last N dates

[H_insight]

Hi all,

I am trying to calculate the total sales for the last 3rd or 2nd day in my sales table based on a selected date. My model have 2 tables:

1- Sales table which holds products, value and dates (some dates are blank).

2-Dates table

I have an active relationship between Dates[Date] & Sales[Date].

My aim is to have a measure to calculate total sales in the last N days from the selected date & ignoring blank dates.

For example when I select the 29/04/2020, I should be able to see the below table:

Not sure how to:

a- Show the last 3 days (29, 22 and 21) in columns dynamicaly.

b- Measure to calaclate the total sales based on the selection.

Hopefully that make sense. Attached is the sample PBIX file.

Many thanks

[v-kelly-msft · Accepted solution]

Hi @H_insight ,

First delete the relationship between table Dates and Sales,see below:(if delete the relationshiop is not a good choice,then create a new calendar table  and use its dates for calculation)

Then create a measure as below:

Measure =
VAR a =
    CALCULATETABLE (
        TOPN (
            3,
            FILTER ( VALUES ( Sales[Date] ), Sales[Date] <= SELECTEDVALUE ( Dates[Date] ) ),
            [Date], DESC
        ),
        ALLSELECTED ( Sales )
    )
RETURN
    IF (
        SELECTEDVALUE ( Dates[Date] ) = BLANK (),
        MAX ( 'Sales'[Values] ),
        CALCULATE ( SUM ( Sales[Values] ), FILTER ( Sales, Sales[Date] IN a ) )
    )

Finally you will see:

For the related .pbix file ,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[H_insight]

 Hi @v-kelly-msft ,

Amazing! Thank you for sharing the solution. It works as expected and most importantently your dax has no impact on the model performance.

I would be greatful if you can share how to get the total sales for:

- Last day of previous month

- Last day of previous week

Kind regards,

Hesham

[H_insight]

Hi @v-kelly-msft ,

Any chance you can help me to get the total sales for:

- Last day of previous month

- Last day of previous week

Many thanks

Hesham

[camargos88]

Hi @H_insight,

Check this file as example: Download PBIX 

The idea is to have a disconected date table and for the value get the last 3 or n dates in your sales table, iterate on sales table and check if is within those 3 or n dates and sum by date.

If you consider it as a solution, please mark as a solution and kudos.

Ricardo

[H_insight]

Hi @camargos88 ,

Thanks for providing your solution. The solution did work, however there were an additonal impact on the model perfomance. It was consumsing a lot of memory, therefore it was not ideal.

Best Regards,

Hesham

[amitchandak]

@H_insight ,

Refer , if this can help

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-3,DAY))