Reply
Frequent Visitor
Posts: 3
Registered: ‎12-02-2018
Accepted Solution

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:

 

initial-situation

 

After a DAX-Function the result should look like this:

 

 

result

 

About help or solution suggestions, I would be very happy!

 

Thanks,

alex

 


Accepted Solutions
Community Support Team
Posts: 1,125
Registered: ‎07-30-2018

Re: get last value from Date

HI, @Devilstar

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:

2018120312.JPG2018120313.JPGDeeper testing

 

Best Regards,

Lin

 

 

View solution in original post


All Replies
Member
Posts: 107
Registered: ‎09-10-2018

Re: get last value from Date

Looks like a good candidate for Fill Up in the Power Query Editor(Transform).

It only works for null entries, remember so you may have to use Replace first.

Frequent Visitor
Posts: 3
Registered: ‎12-02-2018

Re: get last value from Date

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:

result 2.JPG

 

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

 

 

Established Member
Posts: 200
Registered: ‎07-12-2017

Re: get last value from Date

[ Edited ]

Hi @Devilstar,

 

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.

 

Min value for date.png

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

 

 

Community Support Team
Posts: 1,125
Registered: ‎07-30-2018

Re: get last value from Date

HI, @Devilstar

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:

2018120312.JPG2018120313.JPGDeeper testing

 

Best Regards,

Lin

 

 

Highlighted
Frequent Visitor
Posts: 3
Registered: ‎12-02-2018

Re: get last value from Date

thanks! This works :-)

 

Best regards

Alex