Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

return max value within current year

Hello,

 

I'm a beginner with DAX and power bi, and I would appreciate your help.

 

Basically, I have a table with similar columns A,B,C, but I don't know how to create a formula for D.

 

 

 

Basically I have different budget versions within the same year, but I want the report to only display the latest one from the current year. Sometimes we have budgets in the system that have higher version numbers (N) than the current one for this year, so simply displaying the highest number doesn't work.

 

thanks!

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi Anonymous,

     

    Please refer to below calculated column.

    Max N current year =
CALCULATE (
    MAX ( Table7[N] ),
    FILTER ( ALLEXCEPT ( Table7, Table7[proj] ), Table7[year] = YEAR ( TODAY () ) )
)

     

    Best regards,

    Yuliana Gu

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi

       

      Each project has its own versioning in Oracle. What I meant was that for P002, the highest (ie current in 2018) budget version is 3.

       

      However for P001, the current 2018 budget version is 2 (3 is in 2019).

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous,

     

    Please refer to below calculated column.

    Max N current year =
CALCULATE (
    MAX ( Table7[N] ),
    FILTER ( ALLEXCEPT ( Table7, Table7[proj] ), Table7[year] = YEAR ( TODAY () ) )
)

     

    Best regards,

    Yuliana Gu

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      worked perfectly, thank you very much!

       

      C.