Forum Discussion
Running Total Column Based on Multiple Filters/Aggregations
Hi Friends,
I am facing one of the strage scenarios where i need to arrive running total columns for YTD (Fiscal Year) & Last 12 Months Rolling Average.
Below is my table.
| month_end_date | Type | Fiscal end date | Fiscal Start date | Amt |
| 31-07-2019 00:00 | C | 30-06-2020 | 01-07-2019 | 49 |
| 31-08-2019 00:00 | C | 30-06-2020 | 01-07-2019 | 73 |
| 30-09-2019 00:00 | C | 30-06-2020 | 01-07-2019 | 39 |
| 01-01-2020 00:00 | C | 30-06-2020 | 01-07-2019 | 49 |
| 31-07-2019 00:00 | E | 30-06-2020 | 01-07-2019 | 95 |
| 31-08-2019 00:00 | E | 30-06-2020 | 01-07-2019 | 70 |
| 30-09-2019 00:00 | E | 30-06-2020 | 01-07-2019 | 82 |
| 31-10-2019 00:00 | E | 30-06-2020 | 01-07-2019 | 79 |
| 30-06-2020 00:00 | E | 30-06-2020 | 01-07-2019 | 14 |
| 30-11-2020 00:00 | C | 30-06-2021 | 01-07-2020 | 94 |
| 28-02-2021 00:00 | C | 30-06-2021 | 01-07-2020 | 74 |
| 30-06-2021 00:00 | C | 30-06-2021 | 01-07-2020 | 46 |
| 01-07-2020 00:00 | E | 30-06-2021 | 01-07-2020 | 74 |
| 31-10-2020 00:00 | E | 30-06-2021 | 01-07-2020 | 65 |
| 30-01-2021 00:00 | E | 30-06-2021 | 01-07-2020 | 51 |
| 30-06-2021 00:00 | E | 30-06-2021 | 01-07-2020 | 92 |
| 31-07-2021 00:00 | C | 30-06-2022 | 01-07-2021 | 64 |
| 31-01-2022 00:00 | C | 30-06-2022 | 01-07-2021 | 48 |
| 31-01-2022 00:00 | E | 30-06-2022 | 01-07-2021 | 80 |
| 28-02-2022 00:00 | E | 30-06-2022 | 01-07-2021 | 29 |
I need to arrive new column Fiscal Year Running total YTD based on "TYPE" like the below.
| month_end_date | Type | Fiscal end date | Fiscal Start date | Amt | YTD |
| 31-07-2019 00:00 | C | 30-06-2020 | 01-07-2019 | 49 | 49 |
| 31-08-2019 00:00 | C | 30-06-2020 | 01-07-2019 | 73 | 122 |
| 30-09-2019 00:00 | C | 30-06-2020 | 01-07-2019 | 39 | 161 |
| 01-01-2020 00:00 | C | 30-06-2020 | 01-07-2019 | 49 | 210 |
| 31-07-2019 00:00 | E | 30-06-2020 | 01-07-2019 | 95 | 95 |
| 31-08-2019 00:00 | E | 30-06-2020 | 01-07-2019 | 70 | 165 |
| 30-09-2019 00:00 | E | 30-06-2020 | 01-07-2019 | 82 | 247 |
| 31-10-2019 00:00 | E | 30-06-2020 | 01-07-2019 | 79 | 326 |
| 30-06-2020 00:00 | E | 30-06-2020 | 01-07-2019 | 14 | 340 |
| 30-11-2020 00:00 | C | 30-06-2021 | 01-07-2020 | 94 | 94 |
| 28-02-2021 00:00 | C | 30-06-2021 | 01-07-2020 | 74 | 168 |
| 30-06-2021 00:00 | C | 30-06-2021 | 01-07-2020 | 46 | 214 |
| 01-07-2020 00:00 | E | 30-06-2021 | 01-07-2020 | 74 | 74 |
| 31-10-2020 00:00 | E | 30-06-2021 | 01-07-2020 | 65 | 139 |
| 30-01-2021 00:00 | E | 30-06-2021 | 01-07-2020 | 51 | 190 |
| 30-06-2021 00:00 | E | 30-06-2021 | 01-07-2020 | 92 | 282 |
| 31-07-2021 00:00 | C | 30-06-2022 | 01-07-2021 | 64 | 64 |
| 31-01-2022 00:00 | C | 30-06-2022 | 01-07-2021 | 48 | 112 |
| 31-01-2022 00:00 | E | 30-06-2022 | 01-07-2021 | 80 | 80 |
| 28-02-2022 00:00 | E | 30-06-2022 | 01-07-2021 | 29 | 109 |
For an exmaple in the above for TYPE "C" i have running total for the FY July 2019 to June 2020 i.e. 210 (marked bold & purple), similarly for the same FY TYPE "E" also starts its running total and this running total is 340 (marked bold & purple).
Like this for each TYPE within the FY i need to arrive running total column.
And aslo one more column i need to arrive i.e. Last 12 months Average for each TYPE at any given date.
| month_end_date | Type | Fiscal end date | Fiscal Start date | Amt | YTD | Last 12 months Avg |
| 31-07-2019 00:00 | C | 30-06-2020 | 01-07-2019 | 49 | 49 | 49 |
| 31-08-2019 00:00 | C | 30-06-2020 | 01-07-2019 | 73 | 122 | 122 |
| 30-09-2019 00:00 | C | 30-06-2020 | 01-07-2019 | 39 | 161 | 161 |
| 01-01-2020 00:00 | C | 30-06-2020 | 01-07-2019 | 49 | 210 | 210 |
| 31-07-2019 00:00 | E | 30-06-2020 | 01-07-2019 | 95 | 95 | 95 |
| 31-08-2019 00:00 | E | 30-06-2020 | 01-07-2019 | 70 | 165 | 165 |
| 30-09-2019 00:00 | E | 30-06-2020 | 01-07-2019 | 82 | 247 | 247 |
| 31-10-2019 00:00 | E | 30-06-2020 | 01-07-2019 | 79 | 326 | 326 |
| 30-06-2020 00:00 | E | 30-06-2020 | 01-07-2019 | 14 | 340 | 340 |
| 30-11-2020 00:00 | C | 30-06-2021 | 01-07-2020 | 94 | 94 | 143 |
| 28-02-2021 00:00 | C | 30-06-2021 | 01-07-2020 | 74 | 168 | 168 |
| 30-06-2021 00:00 | C | 30-06-2021 | 01-07-2020 | 46 | 214 | 214 |
| 01-07-2020 00:00 | E | 30-06-2021 | 01-07-2020 | 74 | 74 | 414 |
| 31-10-2020 00:00 | E | 30-06-2021 | 01-07-2020 | 65 | 139 | 232 |
| 30-01-2021 00:00 | E | 30-06-2021 | 01-07-2020 | 51 | 190 | 204 |
| 30-06-2021 00:00 | E | 30-06-2021 | 01-07-2020 | 92 | 282 | 282 |
| 31-07-2021 00:00 | C | 30-06-2022 | 01-07-2021 | 64 | 64 | 560 |
| 31-01-2022 00:00 | C | 30-06-2022 | 01-07-2021 | 48 | 112 | 514 |
| 31-01-2022 00:00 | E | 30-06-2022 | 01-07-2021 | 80 | 80 | 223 |
| 28-02-2022 00:00 | E | 30-06-2022 | 01-07-2021 | 29 | 109 | 201 |
Please help me on this here.
@amitchandak @Greg_Deckler @diex @aj1973 @BA_Pete @Jihwan_Kim parry2k Shimflex@alli
Hi Anonymous
You can use the following codes for new columns.
YTD = CALCULATE ( SUM ( 'Table'[Amt] ), ALLEXCEPT ( 'Table', 'Table'[Type] ), DATESYTD ( 'Table'[month_end_date], "6/30" ) )Last 12 months Avg = CALCULATE ( SUM ( 'Table'[Amt] ), ALLEXCEPT ( 'Table', 'Table'[Type] ), DATESINPERIOD ( 'Table'[month_end_date], 'Table'[month_end_date], -12, MONTH ) )Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
2 Replies
Hi Anonymous
You can use the following codes for new columns.
YTD = CALCULATE ( SUM ( 'Table'[Amt] ), ALLEXCEPT ( 'Table', 'Table'[Type] ), DATESYTD ( 'Table'[month_end_date], "6/30" ) )Last 12 months Avg = CALCULATE ( SUM ( 'Table'[Amt] ), ALLEXCEPT ( 'Table', 'Table'[Type] ), DATESINPERIOD ( 'Table'[month_end_date], 'Table'[month_end_date], -12, MONTH ) )Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.v-jingzhang Thank you so much, this work great for me