Forum Discussion

powerbiexpert22's avatar
powerbiexpert22
Impactful Individual
1 year ago
Solved

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 ...
Need Help
  • kushanNa's avatar
    kushanNa
    1 year ago

    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's avatar
anilelmastasi
Super User
1 year ago

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. āœ