DAX help lastnonblank

[RyanBentham]

I have a table with some sample data:

Date		DateIndex		Project		Value
01/01/2020		1		A		5
02/01/2020		2		A		10
03/01/2020		3		A		17
04/01/2020		4		A		25
05/01/2020		5		A		36
06/01/2020		1		B		7
07/01/2020		2		B		12
08/01/2020		3		B		13
09/01/2020		4		B		13
10/01/2020		5		B		20
16/02/2020		1		C		10
17/02/2020		2		C		18
18/02/2020		3		C		23

From this I'm creating a table visual as follows:

Project | Dates		1	2	3	4	5
A		5	10	17	25	36
B		7	12	13	13	20
C		10	18	23

As you can see, project C will have no value for dates 4 & 5 with it not exisiting in the dataset 

What i'm trying to do is create a dax measure that will populate the gaps in the table above with the lastnonblank value for the particular project.     So the outcome would be like the below, with the value of 23 being assigned to dates 4 & 5.

Project | Dates		1	2	3	4	5
A		5	10	17	25	36
B		7	12	13	13	20
C		10	18	23	23	23

Realistically there would be 10 to 20 projects with various dates, but I would want every projects to go to the max available dateindex,  carrying forward any last non blank value

Any ideas?

[v-cazheng-msft · Accepted solution]

Hi @RyanBentham 

You can try the following steps.

1 Create a Calculated table

New Table = CROSSJOIN(VALUES('Table'[Project]),VALUES('Table'[DateIndex]))

2 Create a Measure

Filled Value =

VAR max_index =

    CALCULATE (

        MAX ( 'New Table'[DateIndex] ),

        ALLEXCEPT ( 'New Table', 'New Table'[Project] ),

        NOT ( ISBLANK ( 'New Table'[Value] ) )

    )

VAR max_val =

    CALCULATE (

        MAX ( 'New Table'[Value] ),

        FILTER (

            ALL ( 'New Table' ),

            'New Table'[Project] = SELECTEDVALUE ( 'New Table'[Project] )

                && 'New Table'[DateIndex] = max_index

        )

    )

VAR res =

    IF (

        ISBLANK ( SELECTEDVALUE ( 'New Table'[Value] ) ),

        max_val,

        SELECTEDVALUE ( 'New Table'[Value] )

    )

RETURN

    IF (

        HASONEFILTER ( 'New Table'[Project] ) && HASONEFILTER ( 'New Table'[DateIndex] ),

        res,

        IF (

            HASONEFILTER ( 'New Table'[Project] ),

            CALCULATE (

                SUM ( 'New Table'[Value] ),

                ALLEXCEPT ( 'New Table', 'New Table'[Project] )

            ),

            CALCULATE (

                SUM ( 'New Table'[Value] ),

                ALLEXCEPT ( 'New Table', 'New Table'[DateIndex] )

            )

        )

    )

The result looks like this:

For more details, you can refer the attached pbix.

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RyanBentham]

@v-cazheng-msft   Thanks so much for you time in doing that.    Works a treat!!

[amitchandak]

@RyanBentham , Try a new measure like

if(isblank(max(Table[Value])), calculate(lastnonblankvalue(Table[DateIndex], max(Table[Value])), allexcept(Table, Table[project])), max(Table[Value]))

[RyanBentham]

Thanks, although didn't quite work for me 😞