Filtered table sum

[SIMMI]

Hi,

I was trying to get the sum of all the values recorded in the past 24 months. My knowledge with DAX is very basic. I have applied filters, but it's working at each row level. I want the filters to be applied at the table level. What I'm getting currently is:

whereas what I'm looking for is:

RaisedDate	ClosedDate	MonthSummary	Value	TEST
01/01/2020	05/01/2020	01/01/2020	1	0
02/02/2020	02/02/2020	01/02/2020	3	1
03/03/2020	03/03/2020	01/03/2020	2	4
04/03/2020	01/04/2020	01/04/2020	4	6
01/01/2020	10/04/2020	01/04/2020	2	6
05/06/2020	05/06/2020	01/06/2020	2	12
01/01/2021	02/01/2021	01/01/2021	3	14
01/05/2021	02/05/2021	01/05/2021	2	17
06/06/2021	01/08/2021	01/08/2021	4	19
02/01/2022	03/01/2022	01/01/2022	2	23
03/03/2022	03/03/2022	01/03/2022	3	21
04/04/2022	04/04/2022	01/04/2022	2	22

Kindly help.

Thanks.

[v-yueyunzh-msft · Accepted solution]

Hi , @SIMMI 

Thanks for the sample data you provide.

Here are the steps you can refer to :
(1)My test data is the same as yours.

(2)We can create a measure like this:

Measure = var _cur_month = MAX('Table'[MonthSummary])
var _pre_24month = EOMONTH(_cur_month,-25)
var _t = FILTER( ALLSELECTED('Table') ,'Table'[MonthSummary]>_pre_24month && 'Table'[MonthSummary]< _cur_month)
return
SUMX(_t , [Value])+0

(3)Then we can meet your need:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Mahesh0016]

@SIMMI can you Share the Sheet1 Data.

[SIMMI]

RaisedDate	ClosedDate	MonthSummary	Value
01/01/2020	05/01/2020	01/01/2020	1
01/01/2020	10/04/2020	01/04/2020	2
02/02/2020	02/02/2020	01/02/2020	3
03/03/2020	03/03/2020	01/03/2020	2
04/03/2020	01/04/2020	01/04/2020	4
05/06/2020	05/06/2020	01/06/2020	2
01/01/2021	02/01/2021	01/01/2021	3
01/05/2021	02/05/2021	01/05/2021	2
06/06/2021	01/08/2021	01/08/2021	4
02/01/2022	03/01/2022	01/01/2022	2
03/03/2022	03/03/2022	01/03/2022	3
04/04/2022	04/04/2022	01/04/2022	2