DAX get N'th last non-blank value

5
votes

For any given date, I would like to get an average Sales of the most recent 3 days with non-blank sales. So I need to retrieve not only the last non-blank sales (which might be easy) but I also need to get the second last and third last sales. Generally, I need N'th last sales.

Sample data:


+------------+--------+--------+--------+--------+------------------+
|    Date    | Amount | N'th 1 | N'th 2 | N'th 3 | Expected Results |
+------------+--------+--------+--------+--------+------------------+
| 2021-02-01 |      1 |      1 |        |        |             1.00 |
| 2021-02-02 |      2 |      2 |      1 |        |             1.50 |
| 2021-02-03 |      2 |      2 |      2 |      1 |             1.67 |
| 2021-02-04 |        |      2 |      2 |      1 |             1.67 |
| 2021-02-05 |      3 |      3 |      2 |      2 |             2.33 |
| 2021-02-06 |        |      3 |      2 |      2 |             2.33 |
| 2021-02-07 |        |      3 |      2 |      2 |             2.33 |
| 2021-02-08 |      4 |      4 |      3 |      2 |             3.00 |
| 2021-02-09 |        |      4 |      3 |      2 |             3.00 |
| 2021-02-10 |        |      4 |      3 |      2 |             3.00 |
| 2021-02-11 |        |      4 |      3 |      2 |             3.00 |
+------------+--------+--------+--------+--------+------------------+

The N'th 1 is the last "non-blank" sales. The N'th 2 is the "last but one". The expected result is the average of N1, N2, N3.

Link to sample data file with solutions suggested by accepted answer:
DAX Rolling Average NonBlanks.pbix

2
powerbidax

2 Answers

6
votes

Here's my take (it's a measure):

Non-blank average = 
var curDate = SELECTEDVALUE(Data[Date], MAX(Data[Date]))
var nonBlankTab = FILTER(ALL(Data), NOT(ISBLANK(Data[Amount])) && Data[Date] <= curDate)
var rankedTab = FILTER ( ADDCOLUMNS ( nonBlankTab, "Rank", RANKX ( nonBlankTab, [Date] ) ), [Rank] <= 3 )
return AVERAGEX(rankedTab, [Amount])

EDIT:

Just an explanation:

  • the measure is calculated for the selected date. If no date context is present, the latest date is assumed.
  • Then I filter out the table to contain only rows with non blank sales not later than curDate
  • Then I rank the dates so that the latest 3 dates always receive ranks 1, 2 and 3.
  • Then I filter out all the dates with rank higher than 3
  • Finally, I calculate an average over the remaining 3 data points.

enter image description here

EDIT2:

I simplified the measure a bit - lastSalesDate is not necessary. Also, as per request in the comments, I left the first attempt as it was, and here is the modified version with TOPN instead of ADDCOLUMNS/RANKX/FILTER combo:

Non-blank average = 
var curDate = SELECTEDVALUE(Data[Date], MAX(Data[Date]))
var nonBlankTab = FILTER(ALL(Data), NOT(ISBLANK(Data[Amount])) && Data[Date] <= curDate)
var rankedTab = TOPN(3, nonBlankTab, [Date])
return AVERAGEX(rankedTab, [Amount])

EDIT3:

A more universal version of the measure that just removes filters from Date column, which is actually all we need. No need to butcher all the other filters on the table:

Non-blank average = 
var curDate = SELECTEDVALUE(Data[Date], MAX(Data[Date]))
var nonBlankTab = CALCULATETABLE(FILTER(Data, NOT(ISBLANK(Data[Amount])) && Data[Date] <= curDate), REMOVEFILTERS(Data[Date]))
var rankedTab = TOPN(3, nonBlankTab, [Date])
return AVERAGEX(rankedTab, [Amount])
2
votes

Firs, create these below 3 measures-

n1 = 
VAR current_date = MIN(your_table_name[Date])
VAR first_max_date_with_no_blank = 
CALCULATE(
    MAX(your_table_name[Date]),
    FILTER(ALL(your_table_name),  your_table_name[Date] <= current_date && your_table_name[Amount] <> BLANK())
)

RETURN
CALCULATE(
    SUM(your_table_name[Amount]),
    FILTER(
        ALL(your_table_name),
        your_table_name[Date] = first_max_date_with_no_blank
    )
)

n2 = 
VAR current_date = MIN(your_table_name[Date])

VAR first_max_date_with_no_blank = 
CALCULATE(
    MAX(your_table_name[Date]),
    FILTER(ALL(your_table_name),  your_table_name[Date] <= current_date && your_table_name[Amount] <> BLANK())
)

VAR second_max_date_with_no_blank = 
CALCULATE(
    MAX(your_table_name[Date]),
    FILTER(ALL(your_table_name),  your_table_name[Date] < first_max_date_with_no_blank && your_table_name[Amount] <> BLANK())
)

RETURN
CALCULATE(
    SUM(your_table_name[Amount]),
    FILTER(
        ALL(your_table_name),
        your_table_name[Date] = second_max_date_with_no_blank
    )
)

n3 = 
VAR current_date = MIN(your_table_name[Date])

VAR first_max_date_with_no_blank = 
CALCULATE(
    MAX(your_table_name[Date]),
    FILTER(ALL(your_table_name),  your_table_name[Date] <= current_date && your_table_name[Amount] <> BLANK())
)

VAR second_max_date_with_no_blank = 
CALCULATE(
    MAX(your_table_name[Date]),
    FILTER(ALL(your_table_name),  your_table_name[Date] < first_max_date_with_no_blank && your_table_name[Amount] <> BLANK())
)

VAR third_max_date_with_no_blank = 
CALCULATE(
    MAX(your_table_name[Date]),
    FILTER(ALL(your_table_name),  your_table_name[Date] < second_max_date_with_no_blank && your_table_name[Amount] <> BLANK())
)

RETURN
CALCULATE(
    SUM(your_table_name[Amount]),
    FILTER(
        ALL(your_table_name),
        your_table_name[Date] = third_max_date_with_no_blank
    )
)

Now create this final measure-

average = 

VAR sum_sales = [n1] + [n2] + [n3]
VAR devide_by = IF([n1] = BLANK(),0,1) + IF([n2] = BLANK(),0,1) + IF([n3] = BLANK(),0,1)

RETURN DIVIDE(sum_sales,devide_by)

Here is the final output-

enter image description here