expected value

[powerbiexpert22]

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 

[kushanNa]

Hi @powerbiexpert22 

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"

[powerbiexpert22]

Hi @kushanNa ,

it should be in DAX

[kushanNa · Accepted solution]

Hi @powerbiexpert22 

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

[anilelmastasi]

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. ✅

[powerbiexpert22]

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