Read out the last valid value

[Jan_Trummel]

Hello community,

I need a measure that always reads the last valid value from a table.

I have the following data in my table Factors:

Table "Factors"

As you can see, I don't have a factor in my table for every day.

But now I need a measure for a later calculation that gives me the last factor value for each day in the dimDate table.

The whole thing should look like this (displayed once in Excel). The different colors should help you to understand what exactly I want to achieve:

The result as it might look in Excel

I have created the following measures in Power BI Desktop:

The first measure simply sums the values in the Factors[Factor] column.

Sum Factors = SUM(Factors[Factor])

The next measure actually does the job, but quickly becomes very slow with larger amounts of data:

Factor every day =

VAR vDate = MAX(DimDate[Date])

VAR vResult =

CALCULATE(

    [Sum Factors],

    LASTNONBLANK(

        FILTER(

            ALL(DimDate[Date]),

            DimDate[Date] <= vDate

        ),

        [Sum Factors]

    )

)

RETURN

vResult

Here is the result:

The measure does what it is supposed to do, but becomes very slow with larger amounts of data

I suspect that the calculation is so slow because of the two iterators LASTNONBLANK and FILTER.

Do you have any idea how I can speed up the calculation?

Thank you and have a nice day!

[amitchandak]

@Jan_Trummel , Try like

CALCULATE(
LASTNONBLANK(DimDate[Date],[Sum Factors]),
FILTER(

ALL(DimDate[Date]),

DimDate[Date] <= max(DimDate[Date])

)

)

Else you have to create a table using generate

Table 2 = GENERATE(ADDCOLUMNS('Table', "Next", COALESCE( minx(filter('Table', [Column1] >EARLIER('Table'[Column1])), [Column1]),TODAY())), GENERATESERIES([Column1], [Next]))

[Jan_Trummel]

Hello @amitchandak  and thanks a lot for your answer!

I slightly adjusted the measure by using LASTNONBLANKVALUE instead of LASTNONBLANK.

Factor every das= 

CALCULATE(

    LASTNONBLANKVALUE(

        DimDate[Date], 

        [Sum Factors]

    ),

    FILTER(

        ALL(DimDate[Date]),

        DimDate[Date] <= MAX(DimDate[Date])

    )

)

The reason is that LASTNONBLANK gives the date but I need the result of [Sum Factors].

The measure also works very well in the demo data. In the production system, however, the calculation still takes a relatively long time. Do you have any other ideas?