Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

get last value from Date

Hello,   i am trying to get the last value from a date. This value should be use for the month before which are null.   The initial situation looks like that:     After a DAX-Function the ...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    HI, Anonymous

    You could try this formula to add a column as below:

    Value 2 = 
VAR CurrentCar = 'Table'[Car]
VAR CurrentDate = 'Table'[Month]
VAR CurrentRegion = 'Table'[region]
VAR LastDateWithValue =
    IF (
        ISBLANK ( 'Table'[Value] ) = FALSE (),
        'Table'[month],
        CALCULATE (
            MIN ( 'Table'[month] ),
            FILTER (
                'Table',
                'Table'[car] = EARLIER ( 'Table'[car] )
                    && 'Table'[region] = EARLIER ( 'Table'[region] )
                    &&'Table'[month]>=EARLIER('Table'[month])
                    && ISBLANK ( 'Table'[Value] ) = FALSE ()
            )
        )
    )
RETURN
    VAR A =
        CALCULATE (
            LASTNONBLANK(  'Table'[Value], 'Table'[Value] ),
            FILTER (
                'Table',
                'Table'[Car] = CurrentCar
                    &&  'Table'[Month]  = LastDateWithValue
                    && 'Table'[region] = CurrentRegion
            )
        )
    RETURN
        IF ( ISBLANK ( 'Table'[Value] ), A, 'Table'[Value] )

    Result:

    Deeper testing

     

    Best Regards,

    Lin

     

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

thank you for your suggestion! With the function Fill Up it works (with import from SAP BW).

 

But i want to use in the next time direct query (on SAP BW), so i have to solve it with a function.

 

On this topic there is nearly the same problem case:

https://community.powerbi.com/t5/Desktop/Fill-blanks-with-previous-value/m-p/492572#M229548

 

i change there solotion for my case:

Value2 = 
VAR CurrentCar = 'Table'[Car]
VAR CurrentDate = 'Table'[Month]
VAR CurrentRegion = 'Table'[region]
VAR LastDateWithValue =
    CALCULATE (
        MAX ( 'Table'[Month] );
        FILTER (
            'Table';
            'Table'[Value] <> BLANK ()
                && 'Table'[Car] = CurrentCar
                && 'Table'[Month] = CurrentDate
                && 'Table'[region] = CurrentRegion
        )
    )
Return
    CALCULATE (
        LASTNONBLANK('Table'[Value];'Table'[Value]);
        FILTER (
            'Table';
            'Table'[Car] = CurrentCar
                && 'Table'[Month] > LastDateWithValue
                && 'Table'[region] = CurrentRegion
                      )
    )

Now it looks like that:

 

Unfortunately, it is not the solution yet....

 

if would like to have a formula like this:

value3 = if('Table'[Value]=BLANK();"get last Value";"nothing")

 

Have anyone a solution?

 

Thanks,

alex

 

 

v-lili6-msft's avatar
v-lili6-msft
Community Support
7 years ago

HI, Anonymous

You could try this formula to add a column as below:

Value 2 = 
VAR CurrentCar = 'Table'[Car]
VAR CurrentDate = 'Table'[Month]
VAR CurrentRegion = 'Table'[region]
VAR LastDateWithValue =
    IF (
        ISBLANK ( 'Table'[Value] ) = FALSE (),
        'Table'[month],
        CALCULATE (
            MIN ( 'Table'[month] ),
            FILTER (
                'Table',
                'Table'[car] = EARLIER ( 'Table'[car] )
                    && 'Table'[region] = EARLIER ( 'Table'[region] )
                    &&'Table'[month]>=EARLIER('Table'[month])
                    && ISBLANK ( 'Table'[Value] ) = FALSE ()
            )
        )
    )
RETURN
    VAR A =
        CALCULATE (
            LASTNONBLANK(  'Table'[Value], 'Table'[Value] ),
            FILTER (
                'Table',
                'Table'[Car] = CurrentCar
                    &&  'Table'[Month]  = LastDateWithValue
                    && 'Table'[region] = CurrentRegion
            )
        )
    RETURN
        IF ( ISBLANK ( 'Table'[Value] ), A, 'Table'[Value] )

Result:

Deeper testing

 

Best Regards,

Lin

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    thanks! This works :-)

     

    Best regards

    Alex