cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
H_insight
Helper III
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:

 
 

test1.png

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
v-kelly-msft
Community Support
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)

Annotation 2020-04-30 154801.png

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:

Annotation 2020-04-30 155334.png

For the related .pbix file ,pls click here.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

View solution in original post

6 REPLIES 6
v-kelly-msft
Community Support
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)

Annotation 2020-04-30 154801.png

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:

Annotation 2020-04-30 155334.png

For the related .pbix file ,pls click here.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

 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

 

 

 

 

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
Community Champion
Community Champion

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



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

Proud to be a Super User!



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
Super User
Super User

@H_insight ,

Refer , if this can help

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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 !!

Helpful resources

Announcements
Microsoft Build 768x460.png

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_carousel_with_text (1).png

Charticulator Design Challenge

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

May UG Leader Call Carousel 768x460.png

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.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!