Forum Discussion
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 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
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
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 )
7 Replies
Hi Anonymous
can you please provide sample data?
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 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
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 )