YTD Cumulative

[Anonymous]

Hello,

I would like to make a YTD Cumulative by year without taking into account the year that is in my filter. For example if I filter on April 2022, to have the YTD accumulation that appears on 2020/2021/2022.

Would you know?

[Anonymous]

Thanks for your help

But what I need is a cumulative based on month no matter year filter.

For example, I click on April 2020, I need to get the countrows for April 2020, 2021,2022.

[v-zhangti · Accepted solution]

Hi, @Anonymous 

You can create a new date table without establishing a relationship between the two tables. Please refer to the attachment for sample data.

Measure = 
VAR _maxyear = CALCULATE ( SUM ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table' ),
            [Year] = MAX ( 'Table'[Year] )
                && [Month] <= SELECTEDVALUE ( 'Date'[Month] ) ) )
VAR _otheryear = CALCULATE ( SUM ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table' ), [Year] = SELECTEDVALUE ( 'Table'[Year] ) ) )
RETURN
    IF ( SELECTEDVALUE ( 'Table'[Year] )
            = CALCULATE ( MAX ( 'Table'[Year] ), ALL ( 'Table' ) ),
        _maxyear,
        _otheryear
    )

Result:

The choice of year does not change the values for 2020 and 2021.

Only the choice of month will change the cumulative value for 2022.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , I think you need cumulative 

with help from date table

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

or refer window function

Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc