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

 

 

affan's avatar
affan
Solution Sage
7 years ago

Hi Anonymous,

 

You can use the below DAX to create the custom column.

 

Value2 = 
VAR CurrentCar = 'Table'[Car]
VAR CurrentDate = 'Table'[Month]
VAR CurrentRegion = 'Table'[region]
VAR _LastDateWithValue =
    CALCULATE (
        MIN ( 'Table'[Month] ),
        FILTER (
            'Table',
            'Table'[Value] <> BLANK ()
                 && 'Table'[Car] = CurrentCar
                && 'Table'[Month] > CurrentDate
                && 'Table'[region] = CurrentRegion
        )
    )
var _result=   IF(ISBLANK('Table'[Value]), CALCULATE (
        FIRSTNONBLANK('Table'[Value],1),
        FILTER (
            'Table',
            'Table'[Car] = CurrentCar
                && 'Table'[Month] = _LastDateWithValue
                && 'Table'[region] = CurrentRegion
                      )
    ),'Table'[Value])
Return
_result

Below is the result I have got from the above expression.

 

You can see the pbix file for your reference.

 

 

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

 

Regards,

Affan