Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 bel...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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
    )

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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?