Modify measure to get last date

[Anonymous]

Achieved Savings Current =
VAR selectedYear =
    SELECTEDVALUE ( AuxDates[Year] )
VAR selectedYM =
    SELECTEDVALUE ( AuxDates[Year MonthNr] )
VAR selectedDate =
    SELECTEDVALUE ( AuxDates[Date] )
VAR currentYear =
    SELECTEDVALUE ( Dates[Year] )
VAR currentYM =
    SELECTEDVALUE ( Dates[Year MonthNr] )
VAR currentDate =
    SELECTEDVALUE ( Dates[Date] )
RETURN
    IF (
        currentYear = selectedYear,
        SWITCH (
            TRUE (),
            currentYM < selectedYM,
                IF (
                    EOMONTH ( currentDate, 0 ) = currentDate,
                    VAR totFDLM =
                        CALCULATE (
                            [Total  Saving],
                            'Savings Details'[LPD] = EOMONTH ( currentDate, 0 ),
                            Dates[Year MonthNr] = currentYM,
                            ALL ( Dates[Date] )
                        )
                    VAR totFDLD =
                        CALCULATE (
                            [Total  Saving],
                            'Deposit Details'[LPD]
                                = EOMONTH ( currentDate, 0 ) - 1,
                            Dates[Year MonthNr] = currentYM,
                            ALL ( Dates[Date] )
                        )
                    RETURN
                        IF ( ISBLANK ( totFDLM ), totFDLD, totFDLM )
                ),
            currentYM = selectedYM,
                IF (
                    currentDate = selectedDate,
                    CALCULATE (
                        [Total  Saving],
                        'Deposit Details'[LPD] = currentDate,
                        Dates[Year MonthNr] = currentYM,
                        ALL ( Dates[Date] )
                    )
                )
        )
    )

I'm using the following part of the above code to get month-end deposits according to 'Deposits Details'[LPD] date columns or if the month-end date is not available, I calculate the day before month-end. But some months even month-end and day before month-end data not available as per the 'Deposits Details'[LPD] column. So, I want to modify this measure to the max date available of the 'Deposits Details'[LPD] date column.

                   VAR totFDLM =
                        CALCULATE (
                            [Total  Saving],
                            'Savings Details'[LPD] = EOMONTH ( currentDate, 0 ),
                            Dates[Year MonthNr] = currentYM,
                            ALL ( Dates[Date] )
                        )
                    VAR totFDLD =
                        CALCULATE (
                            [Total  Saving],
                            'Deposit Details'[LPD]
                                = EOMONTH ( currentDate, 0 ) - 1,
                            Dates[Year MonthNr] = currentYM,
                            ALL ( Dates[Date] )
                        )
                    RETURN
                        IF ( ISBLANK ( totFDLM ), totFDLD, totFDLM )

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

Create a calculated column [yearmonth] then you could use ALLEXCEPT() function to get the last exit date for each yearmonth. And you could directly get the value of this last exit date.

yearmonth = FORMAT('Table'[date],"YYYYMM")

Measure = 
var max_date = CALCULATE(MAX('Table'[date]),ALLEXCEPT('Table','Table'[yearmonth]))
return
CALCULATE(SUM('Table'[value]),FILTER(ALL('Table'),max_date='Table'[date]))

 

Best Regards,

Jay

[amitchandak]

@Anonymous , Try like

CALCULATE ([Total Saving],filter(ALL ( Dates[Date] ), Dates[Date] =max(Dates[Date])))

CALCULATE (lastnonblankvalue(Dates[date],[Total Saving]),ALLselected( Dates[Date] ))

Also switch all and allselected and check

I think allexcept(Dates,Dates[Year MonthNbt]) can be explored

CALCULATE (lastnonblankvalue(Dates[date],[Total Saving]),allexcept(Dates,Dates[Year MonthNbt]))