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:

 

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

 

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

     

     

5 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    7 years ago

    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.

    • 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