Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

Reply
Anonymous
Not applicable

Running Total Column Based on Multiple Filters/Aggregations

‎02-17-2022 02:33 AM

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_dateTypeFiscal end dateFiscal Start dateAmt
31-07-2019 00:00C30-06-202001-07-201949
31-08-2019 00:00C30-06-202001-07-201973
30-09-2019 00:00C30-06-202001-07-201939
01-01-2020 00:00C30-06-202001-07-201949
31-07-2019 00:00E30-06-202001-07-201995
31-08-2019 00:00E30-06-202001-07-201970
30-09-2019 00:00E30-06-202001-07-201982
31-10-2019 00:00E30-06-202001-07-201979
30-06-2020 00:00E30-06-202001-07-201914
30-11-2020 00:00C30-06-202101-07-202094
28-02-2021 00:00C30-06-202101-07-202074
30-06-2021 00:00C30-06-202101-07-202046
01-07-2020 00:00E30-06-202101-07-202074
31-10-2020 00:00E30-06-202101-07-202065
30-01-2021 00:00E30-06-202101-07-202051
30-06-2021 00:00E30-06-202101-07-202092
31-07-2021 00:00C30-06-202201-07-202164
31-01-2022 00:00C30-06-202201-07-202148
31-01-2022 00:00E30-06-202201-07-202180
28-02-2022 00:00E30-06-202201-07-202129

 

I need to arrive new column Fiscal Year Running total YTD based on "TYPE" like the below.

month_end_dateTypeFiscal end dateFiscal Start dateAmtYTD
31-07-2019 00:00C30-06-202001-07-20194949
31-08-2019 00:00C30-06-202001-07-201973122
30-09-2019 00:00C30-06-202001-07-201939161
01-01-2020 00:00C30-06-202001-07-201949210
31-07-2019 00:00E30-06-202001-07-20199595
31-08-2019 00:00E30-06-202001-07-201970165
30-09-2019 00:00E30-06-202001-07-201982247
31-10-2019 00:00E30-06-202001-07-201979326
30-06-2020 00:00E30-06-202001-07-201914340
30-11-2020 00:00C30-06-202101-07-20209494
28-02-2021 00:00C30-06-202101-07-202074168
30-06-2021 00:00C30-06-202101-07-202046214
01-07-2020 00:00E30-06-202101-07-20207474
31-10-2020 00:00E30-06-202101-07-202065139
30-01-2021 00:00E30-06-202101-07-202051190
30-06-2021 00:00E30-06-202101-07-202092282
31-07-2021 00:00C30-06-202201-07-20216464
31-01-2022 00:00C30-06-202201-07-202148112
31-01-2022 00:00E30-06-202201-07-20218080
28-02-2022 00:00E30-06-202201-07-202129109

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_dateTypeFiscal end dateFiscal Start dateAmtYTDLast 12 months Avg
31-07-2019 00:00C30-06-202001-07-2019494949
31-08-2019 00:00C30-06-202001-07-201973122122
30-09-2019 00:00C30-06-202001-07-201939161161
01-01-2020 00:00C30-06-202001-07-201949210210
31-07-2019 00:00E30-06-202001-07-2019959595
31-08-2019 00:00E30-06-202001-07-201970165165
30-09-2019 00:00E30-06-202001-07-201982247247
31-10-2019 00:00E30-06-202001-07-201979326326
30-06-2020 00:00E30-06-202001-07-201914340340
30-11-2020 00:00C30-06-202101-07-20209494143
28-02-2021 00:00C30-06-202101-07-202074168168
30-06-2021 00:00C30-06-202101-07-202046214214
01-07-2020 00:00E30-06-202101-07-20207474414
31-10-2020 00:00E30-06-202101-07-202065139232
30-01-2021 00:00E30-06-202101-07-202051190204
30-06-2021 00:00E30-06-202101-07-202092282282
31-07-2021 00:00C30-06-202201-07-20216464560
31-01-2022 00:00C30-06-202201-07-202148112514
31-01-2022 00:00E30-06-202201-07-20218080223
28-02-2022 00:00E30-06-202201-07-202129109201

 

Please help me on this here.

 @amitchandak @Greg_Deckler @diex @aj1973 @BA_Pete @Jihwan_Kim @parry2k @Shimflex@alli

 

Labels:
Message 1 of 3
334 Views
0
1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

‎02-20-2022 11:43 PM

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 )
)

vjingzhang_0-1645429337594.png

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

 

View solution in original post

Message 2 of 3
329 Views
0
2 REPLIES 2
v-jingzhang
Community Support
Community Support

‎02-20-2022 11:43 PM

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 )
)

vjingzhang_0-1645429337594.png

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

 

Message 2 of 3
330 Views
0
Anonymous
Not applicable
In response to v-jingzhang

‎02-21-2022 09:28 PM

@v-jingzhang Thank you so much, this work great for me

Message 3 of 3
324 Views
0

Helpful resources

Announcements
Power BI DataViz World Championships

Power BI Dataviz World Championships

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!

December 2025 Power BI Update Carousel

Power BI Monthly Update - December 2025

Check out the December 2025 Power BI Holiday Recap!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All