Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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:
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:
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!
@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]))
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])
)
)
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |