DAX previous non empty day

Question

Sorry if I missed similar question/answer.

Basically I am trying to do a day to day comparison in DAX, but cannot find a good way to get the measure for previous non empty day.

I tried PREVIOUSDAY function, but when there is a gap in days, for instance, if there is no sale on Sat & Sun, the result is not what I need

PreviousAmount = CALCULATE([Total $ Amount], PREVIOUSDAY('Fact'[Date]))

What I can think of is to add a helper column in the date dimension to indicate previous non empty date (i.e. if the date is Monday, then previous non empty date will be previous Friday). Then I can use CALCULATE function and filter by the non empty date.

But instead of doing that, is there any way to do the calculation on the fly? Thanks in advance.

Below table should reflect what I want to achieve:

<table border="1">
    <th>Date</th><th>Amount</th><th>What I got</th><th>What I hope</th>
    <tr>
      <td>01/07/2016</td>
      <td>7983</td>
      <td></td>
      <td></td>
    </tr>
  <tr>
      <td>04/07/2016</td>
      <td>15933</td>
      <td></td>
      <td>7983</td>
    </tr>
  <tr>
  <tr>
      <td>05/07/2016</td>
      <td>38591</td>
      <td>15933</td>
      <td>15933</td>
    </tr>
  <tr>
      <td>06/07/2016</td>
      <td>7859</td>
      <td>38591</td>
    <td>38591</td>
    </tr>
  <tr>
      <td>07/07/2016</td>
      <td>3252</td>
      <td>7859</td>
    <td>7859</td>
    </tr>
  <tr>
      <td>07/07/2016</td>
      <td>9474</td>
      <td>3252</td>
    <td>3252</td>
    </tr>
</table>

Rory Rory · Accepted Answer · 2016-07-16T15:53:54

It may not be the neatest but you can create a calculated column to store the previous day value

PreviousDate =
CALCULATE (
    MAX ( [Date] ),
    FILTER ( AmountTable, AmountTable[Date] < EARLIER ( AmountTable[Date] ) )
)

and then create a new calculated column for the amount for that previous day

=
CALCULATE (
    SUM ( [Amount] ),
    FILTER (
        ALL ( AmountTable ),
        AmountTable[Date] = EARLIER ( AmountTable[PreviousDate] )
    )
)

DAX previous non empty day

1 Answers