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

user_1903793 0
Viewed 2329

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:

enter image description here

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:

enter image description here

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()
    )
)
dax powerbi-desktop powerbi
3 Answers

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.

user_98509700

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 )
user_7652260

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()
    )
)
user_19037930
Related