Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Dax Help

Hi Team,   Am trying to build a report that contain a table with several columns(include meassure also). I have some slicers including date also. The table is like above attached one. I ha...
General Comment
  • ryan_mayu's avatar
    ryan_mayu
    4 years ago

    Anonymous 

    pls  try this

    Measure 2 = 
VAR _year=max('Table'[Year])-1
return CALCULATE(sum('Table'[Number Od Devices]),FILTER(ALLEXCEPT('Table','Table'[Account Name ]),'Table'[Year]=_year))

    pls see the attachment below

  • tamerj1's avatar
    tamerj1
    4 years ago

    Anonymous 
    You mean something like this?

    Number of Devises = SUM ( Data[Number Od Devices] )
    Number of Devices Last Year = 
CALCULATE (
    [Number of Devises],
    Data[Year] = MAX ( Data[Year] ) - 1
)
tamerj1's avatar
tamerj1
Community Champion
4 years ago

Hi Anonymous 

can you please provide sample data?

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi tamerj1 

 

Here is the sample data

 

Account Name Number Od DevicesMonthYear
AA175122021
BB169112021
CC15912022
DD25622022
EE25832022
FF12342022
AA23612022
BB3612022
AA5622022
BB36022022
  • ryan_mayu's avatar
    ryan_mayu
    Super User
    4 years ago

    Anonymous 

    pls  try this

    Measure 2 = 
VAR _year=max('Table'[Year])-1
return CALCULATE(sum('Table'[Number Od Devices]),FILTER(ALLEXCEPT('Table','Table'[Account Name ]),'Table'[Year]=_year))

    pls see the attachment below

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks tamerj1 

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks ryan_mayu 

      • ryan_mayu's avatar
        ryan_mayu
        Super User
        4 years ago

        you are welcome