Summing data before 12 Months ago

[croz]

I was wondering if anyone could help me with a problem I have, this is a fairly difficult question to explain so I apologise if it does not make sense at first.

I need to sum data up in a column which only shows data from before 12 months ago or from the selected date.

I already have a column which displays data which ranges from 1 - 12 months, I have implemented a slicer and whichever date is selected the data will range from that date (-12) months. e.g if I select '06/01/2023', the table will show all data from '06/01/2022' - to '06/01/2023'.

However, I need another column which compares with the above column, but instead when i select '06/01/2023' the results show all data from '06/01/2021' - to '06/01/2022'.

I have this DAX code which displays the data from the current date selected and the 12 months prior;

`Current Point =

Var CurrentDate = MAX('Date'[Expirydate])

Var PreviousDate = DATE(YEAR(CurrentDate),MONTH(CurrentDate)-12,DAY(CurrentDate))

VAR Result =

CALCULATE(

Sum('table'[column_name]),

FILTER(

'table',

'table'[Expirydate] >= PreviousDate && 'table'[Expirydate] <= CurrentDate

)

)

RETURN

Result`

the 'Month(CurrentDate)-12' allows the column to display the data ranging 12 months ago to the specific date.

Is there any way I can implement some code which allows me to sum the data from 24 - 12 months ago, but without showing the most recent 12 months.

Thanks All,

[FreemanZ · Accepted solution]

hi @croz 

following your logic, why not try:

PY Point =

Var CurrentDate = MAX('Date'[Expirydate])

Var _Start = DATE(YEAR(CurrentDate),MONTH(CurrentDate)-12,DAY(CurrentDate))

Var _end = DATE(YEAR(CurrentDate),MONTH(CurrentDate)-24,DAY(CurrentDate))

VAR Result =

CALCULATE(

Sum('table'[column_name]),

FILTER(

'table',

'table'[Expirydate] >= _Start && 'table'[Expirydate] <= _end

)

)

RETURN

Result

[croz]

Hi @FreemanZ 

Thank you so much for your suggestion! this has worked as expected! 

Have a great day!