Calculate cumulative avg from the third year of my selection

[MCacc]

Hello there, I have an issue calculating the average of a field starting from the THIRD year and onwards of my selection. 

I have a filter year in my dashboard which corresponds to the year of the column date YEAR I have in my table

I need to calculate my average of the column MOVEMENTS

My average is calculated in a measure as follow:

for the first year (2020) and second-year(2021) of my selection is 

DIVIDE(SUM(TAB[MOVEMENTS]), DISTINCTCOUNT(TAB[CLIENT_ID]))

So

2020: 16/2 = 4

2021 = 16/2 = 4

For the THIRD year of my selection (2022) should be as follow:

2022 = 4 

2022 = 4 + 4 = 8

Meaning, in the THIRD year of my selection I need to sum the average of the year before + the average of the third year and so on. 

CLIENT ID	TRANSACTION NUMBER	PRODUCT_CODE	MOVEMENTS	YEAR
3	345	p1	4	11/12/2020
1	346	p1	4	11/12/2020
2	347	p1	4	11/12/2020
2	348	p1	4	11/12/2020
1	37	p1	4	11/12/2021
1	348	p1	4	11/12/2021
2	346	p1	4	11/12/2021
2	347	p1	4	11/12/2021
1	37	p1	4	11/12/2022
1	348	p1	4	11/12/2022
2	346	p1	4	11/12/2022
4	367	p1	4	11/12/2022

Any idea how can I calculate this?

Thank you

[v-eqin-msft]

Hi @MCacc ,

Sorry to disturb you...

But did I answer your question ? Please mark my reply as solution. Thank you very much.

Best Regards,
Eyelyn Qin

[v-eqin-msft]

Hi @MCacc ,

According to my understanding, you want the expected output as shown below, right?

Please follow these steps:

1. Add Avg column in original table.

Avg =
CALCULATE (
    DIVIDE ( SUM ( 'Table'[MOVEMENTS] ), DISTINCTCOUNT ( 'Table'[CLIENT ID] ) ),
    ALLEXCEPT ( 'Table', 'Table'[YEAR].[Year] )
)

2. Create a new table:

Table 2 =
DISTINCT ( SELECTCOLUMNS ( 'Table', "Year", YEAR ( [YEAR] ) ) )

3. Add some necessary columns to it :

Rank =
RANKX ( ALL ( 'Table 2' ), [Year],, ASC, DENSE )

Avg =
LOOKUPVALUE ( 'Table'[Avg], 'Table'[YEAR].[Year], [Year] )

Result =
IF (
    [Rank] >= 3,
    CALCULATE (
        SUM ( 'Table 2'[Avg] ),
        FILTER (
            'Table 2',
            'Table 2'[Rank] > 1
                && 'Table 2'[Rank] <= EARLIER ( 'Table 2'[Rank] )
        )
    ),
    [Avg]
)

Please take a look at the pbix file here.

Best Regards,
Eyelyn Qin

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

[AlB]

@MCacc 

I keep getting really confused with third year, forth year, selected year....

I think the code above should give you the basis for getting where you want to get. Otherwise, if you share a mock pbix with the simplified model and show there a couple of scenarios, explaining what the expected result should be and the logic for it, I might be able to understand

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[MCacc]

Hello Aib, 

this is what I want to achieve:

1st year avg = 4

2nd year avg = 3

3rd year avg = 2,5 + 3(2nd avg) = 5,5

4th year avg = 3 + 5,5 (2nd and 3rd year avg) = 8,5

5th year avg = 2 + 8,5 (2nd, 3rd, 4th year avg) = 10,5

Your dax returns blank values. 

Besides that, I tried this dax:

var firstyear = YEAR(MIN(CAL[YEAR]))

var SECONDYEAR =YEAR(MIN(CAL[YEAR]))+1

var THIRDYEAR = YEAR(MIN(CAL[ANNO]))+2

VAR SELECETIONYEAR = SELECTEDVALUE(CAL[YEAR])

var simple_avg= CALCULATE(DIVIDE(SUM(TAB[MOV]), DISTINCTCOUNT(TAB[TRANSACTION_NUMBER]),0))

var avgafterthirdyear =CALCULATE(DIVIDE(SUM(TAB[MOV]), DISTINCTCOUNT(TAB[TRANSACTION_NUMBER]),0), FILTER(TABELLA, SELECETIONYEAR >= THIRDYEAR), ALLEXCEPT(CAL,CAL[YEAR]))

RETURN IF(SELECETIONYEAR = firstyear || SELECETIONYEAR = SECONDYEAR, simpleavg, if(SELECETIONYEAR >= THIRDYEAR, simple_avg + avgafterthirdyear, BLANK()))

But it doesn't work because it returns the avg value for the current selection 

[MCacc]

Hi Abi, 

sorry for my mistake, it's 16/4 because 16 is the sum of my column movements and 4 is my distinct count of my client_id. So the value for my avg is 4.

I've already tried something similar to your dax formula, unfortunately, the sum in the return value is calculated each year so it repeats itself twice. 

So if 2020 is 4 and 2021 is 5 I'll have 4 + 5, which is not my case because in 2020 I should have 4 and in 2021 I should have 5.

The sum should happen only when I'm in my third year. So if I have 2022 = 3, the sum in the return value should work only with 2021 = 5 + 2022 = 3.

I'd like to find a way to tell my dax: are you in your third year? Ok, sum the value from this third year (selected value) and the year before. Are you in your fourth year? Sum the value from this fourth year (selected value), the third year, and the second year... 

[AlB]

@MCacc 

Still quite unclear.

2020: 16/2 = 4

2021 = 16/2 = 4

First time I see 16/2 is 4. Plus in 2020 there are 3 different client IDs

Try this, you can tweak it to adapt it exactly to your needs. It assumes a relationship betwenyour date and fact table

Measure =
VAR selectedYear_ =
    SELECTEDVALUE ( Date[Year] )
VAR _1stYear_ = selcetedYear_ - 2
VAR _2ndYear_ = selectedYear_ - 1
VAR val1_ =
    CALCULATE (
        DIVIDE ( SUM ( TAB[MOVEMENTS] ), DISTINCTCOUNT ( TAB[CLIENT_ID] ) ),
        Date[Year] = _1stYear
    )
VAR val2_ =
    CALCULATE (
        DIVIDE ( SUM ( TAB[MOVEMENTS] ), DISTINCTCOUNT ( TAB[CLIENT_ID] ) ),
        Date[Year] = _2ndYear
    )
RETURN
    val1_ + val2_

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

Hi @MCacc 

Ok, but what is your "selection" for the example above?

Is it what you select on a slicer with Year?

Is it one year (2020) ?

Do you select the 3 years (2020,21,22)?

What does  THIRD year and onwards mean?? What would hapen if you had years further than 2022??

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[MCacc]

Hey Aib I select only one year, I don't have multiple selections. 

Onwards means that if in my selection I have 2023, my measure will include 2021, 2022, 2023. I in my selection I have 2024 my measure will include 2021, 2022, 2023, 2024.

This period will be max 5 years, so 2020, 2021, 2022, 2023, 2024