Prior Days Sales (not previous day)

[Anonymous]

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:

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

[Anonymous · Accepted solution]

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
)

[Anonymous]

Thank you. I needed that too! 

[Anonymous]

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

[d_gosbell]

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?

[Anonymous]

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!

[d_gosbell]

---

@Anonymous 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 

[amitchandak]

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))

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

[Anonymous]

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?