topic dax formula to complete table with lastnoblank value in DAX Commands and Tips

dax formula to complete table with lastnoblank value

GiuseppeTE — Thu, 15 Aug 2019 20:30:50 GMT

Hello,

this is my first time in the comunity and I am a new user of power bi.

The issue I would like to solve is the follow:

I have to table like below:

where the table 1 have a calculated column with the following formula:

Output calculated = SUMX(

RELATEDTABLE(Tabella2);

Tabella2[outputactual]

)

now the issue is:

how I can fill the rest of blank value in the column calculated with the last one value (15) ?

below the related table2:

thanks in advance for every one would like help me.

Giuseppe

Re: dax formula to complete table with lastnoblank value

Zubair_Muhammad — Fri, 16 Aug 2019 16:18:36 GMT

GiuseppeTE

Try this column

=
VAR lastavailabledata =
    CALCULATE (
        MAX ( Tabella2[dataworked] ),
        FILTER ( ALL ( Tabella2 ), Tabella2[dataworked] <= Tabella1[data] )
    )
RETURN
    CALCULATE (
        SUM ( Tabella2[outputactual] ),
        FILTER ( ALL ( Tabella2 ), Tabella2[dataworked] = lastavailabledata )
    )

Re: dax formula to complete table with lastnoblank value

GiuseppeTE — Fri, 16 Aug 2019 20:20:29 GMT

Hello Muhammad,

your formula work perfectly.

thanks a lot

Giuseppe

Re: dax formula to complete table with lastnoblank value

Zubair_Muhammad — Fri, 16 Aug 2019 21:29:12 GMT

HI GiuseppeTE

Thanks for the feedback

Please mark the correct answer as solution. I see that you marked your own reply as ANSWER.