Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
croz
Regular Visitor

Summing data before 12 Months ago

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,

1 ACCEPTED SOLUTION
FreemanZ
Super User
Super User

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

View solution in original post

2 REPLIES 2
FreemanZ
Super User
Super User

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

Hi @FreemanZ 

 

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

Have a great day!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors