cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Post Patron
Post Patron

Prior Days Sales (not previous day)

I could use help with a formula…I am trying to write a formula that totals the priors days sales when the prior day is not the previous day.  I have tried numerous variations of DAX formulas like below with no luck.  To illustrate I created a quick excel table example shown below with the raw data on the left on the right the result I am looking for.  I also created a Power BI dashboard for this example.  The second screen shot below shows a Power BI dashboard example with most of the same results except no Prior Total Sales column (using the below formula).  Can someone take a look and help me with the formula?

 

Thx

 

THIS FORMULA DID NOT WORK:

Prior Total Sales =

CALCULATE(

    SUM('Sales Data'[Sales]),

   Filter('Sales Data',

    'Sales Data'[Date]='Measure'[Prior Sales Date])

    )

 

EXCEL DATA/RESULTS:

 

image004.jpg

CANNOT FIGURE OUT FORMULA TO HAVE THE PRIOR DAYS SALES IN POWER BI WHEN THE PRIOR DAY IS NOT THE PREVIOUS DAY.

 

image005.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
Resolver II
Resolver II

Try something like this to get the previous date:

VAR latestDate = MAX('Fact'[SalesDate])
VAR prevDate = CALCULATE(MAX('Fact'[SalesDate]),'Fact'[SalesDate]<latestDate)
RETURN
CALCULATE(
[Sales],
'Date'[Date]=prevDate
)

View solution in original post

8 REPLIES 8
Super User IV
Super User IV

Make sure you have a date dimension table

https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

Then use

last day sales = CALCULATE(sum(Sales[Sales Amount]),DATEADD('Date'[Date Filer],-1,DAY))

Prior_last_day_sales.png

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Super User II
Super User II

It's hard to answer this without knowing more about your other measures and your data model. It's possible that the context inside the filter function is affecting the prior date measure. One possible fix might be to capture this value in a variable (but this is a pure stab in the dark so it may not work)

 

Prior Total Sales =
VAR _priorDate = 'Measure'[Prior Sales Date]
RETURN CALCULATE(
    SUM('Sales Data'[Sales]),
   Filter('Sales Data',
    'Sales Data'[Date]=  _priorDate)
    )

If this does not work can you post the measure expressions for Prior Sales Date, Current Sales Date and Current Sales? Do you have a separate date table or are you trying to use a date column from the 'Sales Data' table?

I have the PBIX and the excel spreadsheet that the PBIX is attached to...how do I attach these files?
The formula you offered was one of the versions that I tried and it did not work.  Thx for jumping in!

The prior date will not be the most current day -1...most cases the prior date is one to two weeks earlier...would this formula work when the prior dates are not consecutive?


@ReynoldsTstan16 wrote:

I have the PBIX and the excel spreadsheet that the PBIX is attached to...how do I attach these files?


There should be a section at the bottom of the reply window that says "Attachments" - it's not obvious, but if you click on the paperclip icon it should prompt you to add a file 

Resolver II
Resolver II

Try something like this to get the previous date:

VAR latestDate = MAX('Fact'[SalesDate])
VAR prevDate = CALCULATE(MAX('Fact'[SalesDate]),'Fact'[SalesDate]<latestDate)
RETURN
CALCULATE(
[Sales],
'Date'[Date]=prevDate
)

View solution in original post

Worked perfectly...thx for relieving weeks of stress!

Thank you. I needed that too! 

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors