Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 ...
Need Help
Tips and Tricks
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    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's avatar
v-jingzhang
Community Support
4 years ago

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.

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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