cancel
Showing results for
Did you mean:
Helper III

## Calculate Total for the last N dates

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

1 ACCEPTED SOLUTION
Community Support

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:

Best Regards,
Kelly
6 REPLIES 6
Community Support

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:

Best Regards,
Kelly
Helper III

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

Helper III

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

Community Champion

Hi @H_insight,

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

Proud to be a Super User!

Helper III

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

Super User

Refer , if this can help

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

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.