Dax Help

[Anonymous]

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 have a date slicer in canvas,so if select 2022 it should show 2022 values in  no of devices column and 2021 in last year no of devices column.

if select 2023 it should show 2023 values in  no of devices column and 2022 in last year no of devices column.

can any help me to write the dax for last year column

Regards,

Nadeem

[ryan_mayu · Accepted solution]

@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 · Accepted solution]

@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]

Hi @Anonymous 

can you please provide sample data?

[Anonymous]

Hi @tamerj1 

Here is the sample data

Account Name	Number Od Devices	Month	Year
AA	175	12	2021
BB	169	11	2021
CC	159	1	2022
DD	256	2	2022
EE	258	3	2022
FF	123	4	2022
AA	236	1	2022
BB	36	1	2022
AA	56	2	2022
BB	360	2	2022

[ryan_mayu · Accepted solution]

@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]

Thanks @tamerj1 

[Anonymous]

Thanks @ryan_mayu 

[ryan_mayu]

you are welcome