DAX Get the last date with positive sales regardless the Date row context

Question

How to get the latest date with sales Amount for all the dates between min and max date with sales Amount. In the table, some Dates may have null Amount. Here is example with expected results:

Here is what I have tried. These are all DAX measures.

LastDate = 
CALCULATE( 
    LASTDATE( Sales[Date] ), 
    REMOVEFILTERS( Sales[Date] ) 
)

LastNonBlank = 
CALCULATE( 
    LASTNONBLANK( Sales[Date], [Sales] ),
    REMOVEFILTERS( Sales )
)

MaxDate = 
CALCULATE( 
    MAX( Sales[Date] ), 
    REMOVEFILTERS( Sales[Date] ) 
)

MaxDate_Filter = 
CALCULATE( 
    MAX( Sales[Date] ),
    FILTER( ALL( Sales ), Sales[Amount] > 0 )
)

And here is what I get with it:

So non of the measures produces the expected results.

Table to recreate problem:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtA1MAQiJR0lEIYLmCjF6iDJGwHl8EgbA+VM8cibAOWM8cibQoxXio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Amount = _t, #"Expected Result" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Amount", Int64.Type}, {"Expected Result", type date}})
in
    #"Changed Type"

Update

Here I found interesting reference that solved my problem:

https://www.sqlbi.com/articles/hiding-future-dates-for-calculations-in-dax/

We add calculated column to Sales table:

DatesWithSales = 
var CalendarDate = Sales[Date]
return
CalendarDate <= CALCULATE( MAX( Sales[Date] ), FILTER( ALLSELECTED( Sales ), Sales[Amount] > 0 ) ) &&
CalendarDate >= CALCULATE( MIN( Sales[Date] ), FILTER( ALLSELECTED( Sales ), Sales[Amount] > 0 ) )

Then we use measure:

Expected Result = 
CALCULATE(
    MAX( Sales[Date] ),
    CALCULATETABLE(
        VALUES( Sales[Date] ), -- here can be whatever time intelligence function like SAMEPERIODLASTYEAR( Sales[Date] )
        Sales[DatesWithSales] = TRUE()
    )
)

Hoopla

Could you explain a bit more the issue as most of them provide the Expected Result? – Elasmobranch

There is a column of expected results marked with red. I need to get 2020-01-04 between min and max date with Amount. My attempts return value for 2020-01-05 which is not correct. Note that the solution should provide value for 2020-01-02 because it is between min and max. – Hoopla

I do see 2020-01-04 as expected in your screenshot? – Elasmobranch

@FabianSchenker Expected Result (ER) is not produces by any of the measures. It can be seen with a naked eye. All the measures produce SOMETHING in the last line of the table visual while ER has null value there. – Hoopla

Answer 1

Your MaxDate_Filter looks fine. If you want to blank out dates beyond that, then you can do

Expected Result =
VAR RowDate = SELECTEDVALUE ( Sales[Date] )
RETURN
    IF ( RowDate <= [MaxDate_Filter], RowDate )

or, assuming you have defined an analogous [MinDate_Filter] and want to filter on both sides:

Expected Result =
VAR RowDate = SELECTEDVALUE ( Sales[Date] )
RETURN
    IF ( RowDate <= [MaxDate_Filter] && RowDate >= [MinDate_Filter], RowDate )

Discarnate

It's a shortcut for IF(HASONEVALUE(Sales[Date]), VALUES(Sales[Date])). You could use MAX instead if you prefer. – Discarnate

Answer 2

you can try:

    Expected Result = 
var maxDate = CALCULATE(MAX(Sales[Date]), FILTER(Sales, NOT(ISBLANK(Sales[Amount]))))
return IF(Sales[Date]> maxDate,BLANK(),maxDate)

It first calcualates the maxDate based on all rows with a value in Amount and later fills the column with maxDate, only when Sales[Date] is smaller or equal.

Toddler

PS: cannot test at this moment, so I hope I have no errors in code.. – Toddler

It does not work. In the last line of your code Sales[Date] raises error. It needs some wrap like MAX( Sales[Date] ). But MAX wrap does not work. – Hoopla

This part is a problem: IF(Sales[Date], not blank. – Hoopla

Are you using measures or are they extra columns? – Toddler

Ok, I have edited my answer and made it clear that I have tried the measures. – Hoopla

Answer 3

Here I found interesting reference that solved the problem:

https://www.sqlbi.com/articles/hiding-future-dates-for-calculations-in-dax/

We add calculated column to Sales table. In real model, this column should be added to Calendar table.

DatesWithSales = 
var CalendarDate = Sales[Date]
return
CalendarDate <= CALCULATE( MAX( Sales[Date] ), FILTER( ALLSELECTED( Sales ), Sales[Amount] > 0 ) ) &&
CalendarDate >= CALCULATE( MIN( Sales[Date] ), FILTER( ALLSELECTED( Sales ), Sales[Amount] > 0 ) )

Then we use measure:

Expected Result = 
CALCULATE(
    MAX( Sales[Date] ),
    CALCULATETABLE(
        VALUES( Sales[Date] ), -- here can be whatever time intelligence function like SAMEPERIODLASTYEAR( Sales[Date] )
        Sales[DatesWithSales] = TRUE()
    )
)

Hoopla