Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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])
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |