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

4
votes

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()
    )
)
3
powerbidaxpowerbi-desktop
Could you explain a bit more the issue as most of them provide the Expected Result? – user12493039
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. – Przemyslaw Remin
I do see 2020-01-04 as expected in your screenshot? – user12493039
@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. – Przemyslaw Remin

3 Answers

2
votes

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 )
2
votes

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.

1
votes

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