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

 

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
    )

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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
    )

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you. I needed that too! 

  • 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?

  • d_gosbell's avatar
    d_gosbell
    Icon for Super User rankSuper User
    6 years ago

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

      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's avatar
        d_gosbell
        Icon for Super User rankSuper User
        6 years ago
        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