Forum Discussion
expected value
i am using two columns yearmonth and value in table visual as shown below, i need third column "expected_output" in same table as highlighted below , this third column should pick up the first non zero value (example 0.05, 0.02 etc) of current row and populate that same value and all previous rows including the current row
This dax code should work
expected_output = VAR temp = TOPN ( 1, FILTER ( Sheet1, Sheet1[yearmonth] >= EARLIER ( Sheet1[yearmonth] ) && Sheet1[value] <> 0 ), Sheet1[yearmonth], ASC ) VAR result = MINX ( temp, Sheet1[value] ) RETURN result
5 Replies
Hello powerbiexpert22 ,
You can use this formula in your new calculated column:
Expected_Output =
VAR CurrentYM = [YearMonth]
VAR NonZeroTable =
FILTER (
ALL ( 'YourTable' ),
'YourTable'[YearMonth] <= CurrentYM && 'YourTable'[Value] <> 0
)
VAR FirstNonZero =
CALCULATE (
MIN ( 'YourTable'[YearMonth] ),
NonZeroTable
)
RETURN
CALCULATE (
MAX ( 'YourTable'[Value] ),
FILTER ( ALL ( 'YourTable' ), 'YourTable'[YearMonth] = FirstNonZero )
)If this solved your issue, please mark it as the accepted solution. ā
Hi anilelmastasi ,
it is returning same value 0.05 in all rows , please see below file
https://drive.google.com/file/d/1BUukgLhD1-p6B5rRE5Gro3sPhq91wW86/view?usp=drive_link
Can you do this on power query side ? then it should be much easier than dax
use this M code steps
let Source = Csv.Document(File.Contents("C:\XXXX\data.csv"),[Delimiter=",", Columns=2, Encoding=65001, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"yearmonth", Int64.Type}, {"value", type number}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"yearmonth", Order.Descending}}), AddedIndex= Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type), // Create a temporary column with 0/null replaced with null, others kept AddedTemp = Table.AddColumn(AddedIndex, "TempValue", each if [value] <> null and [value] <> 0 then [value] else null), // Fill down the cleaned column FilledDown = Table.FillDown(AddedTemp, {"TempValue"}), // Rename filled column Renamed = Table.RenameColumns(FilledDown, {{"TempValue", "FilledValue"}}), #"Sorted Rows1" = Table.Sort(Renamed,{{"yearmonth", Order.Ascending}}) in #"Sorted Rows1"Hi kushanNa ,
it should be in DAX
This dax code should work
expected_output = VAR temp = TOPN ( 1, FILTER ( Sheet1, Sheet1[yearmonth] >= EARLIER ( Sheet1[yearmonth] ) && Sheet1[value] <> 0 ), Sheet1[yearmonth], ASC ) VAR result = MINX ( temp, Sheet1[value] ) RETURN result
