How to build a DAX function to pick the value of previous date when dates are inconsistent

[preetisharma]

Hi,

I'm trying to build a DAX function in my table to show value/amount of previous date when the user selects a calendar date which does not exists in my table.

I have tried to create a new table with calendar dates and use the below function but the issue is that,

Date in my main table has duplicates in it so LASTDATE() does not work

and on using MAX() it picks only the maximum value from the group of serials rather than the previous value.

Cost A on nearest date = 
VAR myFilterDate = 
    LASTDATE( 'table'[Date] )
VAR myDate = 
    LASTDATE( 
        FILTER(
            ALL(Trnx[Date] ), 
            Trnx[Date] <= myFilterDate 
        )
    )
VAR mySumA = 
    CALCULATE( 
        SUM(Trnx[Value]), 
        FILTER(
            ALL(Trnx), 
            Trnx[Date] = myDate 
        )
    )
RETURN mySumA​

Therefore, on above example I want to display 136.80 when selected Date is 10-Mar-20 and 159.60 when selected date is 09-Mar-20.

Do you have any suggestions on how to pick the previous date amount when the user selected slicer date does not exists in the table?

Any help wpuld be much appreciated! @jaidaarellano 

[v-chuncz-msft]

@preetisharma 

You may use MAXX instead. Any question about data shaping, modeling, and report building in the Power BI Desktop app, visit Desktop forum.

[preetisharma]

Hi @v-chuncz-msft 

Thanks for the revert!

I tried using MAXX instead of LASTDATE but I get the below error message -

"A table with multiple values was supplied where a single value was expected"

The Measure I used is:

Cost A on nearest date = 
VAR myFilterDate = 
    LASTDATE( 'table'[Date] )
VAR myDate = 
    MAXX( 'Trnx' ,
        FILTER(
            ALL('Trnx'[Date] ), 
            'Trnx'[Date] <= myFilterDate 
        )
    )
VAR mySumA = 
    CALCULATE( 
        SUM('Trnx'[Value]), 
        FILTER(
            ALL('Trnx'), 
            'Trnx'[Date] = myDate 
        )
    )
RETURN mySumA​

'Trnx'[Date] has duplicate dates as there is more than one value on a single date. Which is why I could not use LASTDATE and MAX, as mentioned above.

May I please know a solution to tackel this? As this report is key for our organization and we are facing this issue which is difficult to resolve because:

1. All Calendar dates does not exists in 'Trnx'[Date]

2. 'Trnx'[Date] has duplicate dates with different serial and value

Your help would be much appreciated!

Regards,

Preeti