Forum Discussion

sentsara's avatar
sentsara
Helper II
8 years ago

Latest BreakEven DAX Expression Value

Hi,

I need to derive "LATEST Break Even" Dax Expression  based on calculated column "Rate Vs Breakeven"

 

DataSet name: QBM

 

Rate Vs Breakeven = if([Current Rate 2019]=0,blank(),if(ISBLANK([Current Rate 2019]),blank(),[Current Rate 2019]-[Monthly Break Even]))

 

Tried with below Expression and thrown "Expression refers to multiple column.Multiple column cannot be converted into scalar value"

 

LATEST Break Even=CALCULATE(MAX([Rate Vs Breakeven],ALL('QSI By Measure')))

 

Basically i need Lastest break even value should be repeated based on Max of "Rave vs Breakeven"

 

 

TRENDMONTHCurrent Rate 2019Monthly Break EvenRate Vs BreakevenLatestBreakevenStatic Final Rate
10.67% 0.67%0.21%0.423357664
24.29%5.42%-1.13%0.21%0.423357664
37.48%7.64%-0.16%0.21%0.423357664
410.74%10.54%0.20%0.21%0.423357664
512.54%12.33%0.21%0.21%0.423357664
6 14.14% 0.21%0.423357664
7 15.46% 0.21%0.423357664
8 16.48% 0.21%0.423357664
9 15.93% 0.21%0.423357664
10 18.35% 0.21%0.423357664
11 19.42% 0.21%0.423357664
12 19.61% 0.21%0.423357664
13 19.61% 0.21%0.423357664
14 21.87% 0.21%0.423357664
15 28.71% 0.21%0.423357664
16 43.36% 0.21%0.423357664
17 44.34% 0.21%0.423357664
18 44.34% 0.21%0.423357664
Need Help

4 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    HI sentsara

     

    Is this what you are after?

     

    Column = 
VAR LatestTrentMonth = MAXX(FILTER('QBM','QBM'[Current Rate 2019]>0),'QBM'[TRENDMONTH])
RETURN MAXX(FILTER('QBM','QBM'[TRENDMONTH] = LatestTrentMonth),'QBM'[Rate Vs Breakeven])
    • sentsara's avatar
      sentsara
      Helper II
      8 years ago

      hi phil,

       

      I'm getting "Circular  Dependency was detected: QBM[Column]

       

      I need the below values:

      LatestBreakeven
      0.21%
      0.21%
      0.21%
      0.21%
      0.21%
      0.21%
      0.21%
      0.21%
      0.21%
      0.21%
      0.21%
      0.21%
      0.21%
      0.21%
      0.21%
      0.21%
      0.21%
      0.21%
  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    8 years ago

    Hi sentsara

    Try this 

    lastest = CALCULATE(MAX([TRENDMONTH]),FILTER(ALL('Latest BreakEven'),[Current Rate 2019]<>BLANK()))
    LatestBreakeven = CALCULATE(MAX([Rate Vs Breakeven]),FILTER(ALL('Latest BreakEven'),[TRENDMONTH]=[lastest]))

     

     

    Best Regards

    Maggie