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: 

Get Fabric Certified for FREE during Fabric Data Days. Don't miss your chance! 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
303 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
298 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
299 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
293 Views
0

Helpful resources

Announcements
Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

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
Top Solution Authors
View All