Forum Discussion
Filtered table sum
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.
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
3 Replies
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
SIMMI can you Share the Sheet1 Data.
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
