Compounding values in a power BI measure

[danielgergely]

Hey everyone!

I have a question.

I created a DAX measure, which divides two values, and gives me a percentage. This percentage changes every week. So far so good. 

However what I want to achieve, is to make a compund percentage. Last weeks value added to this weeks value divided by two.

The values I would like to have is for example week 29: (-73.37) + (-72.26))/2= -73.35  Week 30: -31.99 and so on.

Is there a way to achieve this?

Thanks in advance!

[v-lili6-msft · Accepted solution]

hi @danielgergely 

For your case, you could try this way as below:

Step1:

Add an index column by yearweek column ([Year]*100+[WeekNum]) in date table.

Index = RANKX('Date',('Date'[Year]*100+'Date'[Weeknum]),,ASC,Dense)

Step2:

Use this logic to get your requirement:

Result =
 (
    CALCULATE (
        [Your percentage measure],
        FILTER ( ALL ( 'Date' ), 'Date'[Index] = MAX ( 'Date'[Index] ) - 1 )
    ) + [Your percentage measure]
) / 2

By the way, the result should be 29: (-73.37) + (-72.26))/2= -73.315  Week 30: -40.81 and so on

If not your case, please share your sample pbix file and expected output.

Regards,

Lin

[danielgergely]

After many Trials and erros, I finally got the result I want. This post gave me the basis for my sollution. 

Thanks a LOT for your help!

[danielgergely]

Hi @v-lili6-msft ,

Thanks for your reply.

I did all the steps you said. Created an Index column in my Date table (which gives me the X axis). and ordered ist ascending.

Then I created mey Result measure in the original table, specifying the filters for my date table. I got some results, but not the correct values. What did I do wrong?

 Index column code:

Index2 = RANKX('YEAR_Cheatsheet';('YEAR_Cheatsheet'[Year]*100+'YEAR_Cheatsheet'[Weeknumber]);;ASC;Dense)

The measure code:

ResultM =

(

    CALCULATE (

        [Ist/Soll%];

        FILTER ( ALL ( 'YEAR_Cheatsheet' ); 'YEAR_Cheatsheet'[Index2] = MIN ( 'YEAR_Cheatsheet'[Index2] ) + 1 )

    ) + [Ist/Soll%]

) / 2

The results I got

Week 28:  -0.87

Week 29:  -0.86

Week 30: -0.55

Week 31: -0.98

Week 32: -0.69

Week 33: -0.68

Week 33: -0.61

...

I can sadly not sare the .pbx file. It contains sensitive inforamation...

Thanks a LOT for your help!

[v-lili6-msft]

hi @danielgergely 

Use this formula separately to if it return the last week  [Ist/Soll%]

Lastweek1 =

    CALCULATE (

        [Ist/Soll%];

        FILTER ( ALL ( 'YEAR_Cheatsheet' ); 'YEAR_Cheatsheet'[Index2] = MIN ( 'YEAR_Cheatsheet'[Index2] ) + 1 )

    )

or 

Lastweek2 =

    CALCULATE (

        [Ist/Soll%];

        FILTER ( ALL ( 'YEAR_Cheatsheet' ); 'YEAR_Cheatsheet'[Index2] = MAX ( 'YEAR_Cheatsheet'[Index2] ) - 1 )

    )

If possible, could you please share a simple sample pbix file that just use virtual data.

Regards,

Lin

[danielgergely]

Hi @v-lili6-msft 

Thanks again for your reply.

Maybe its helpful if I share my measures:

Index2 = RANKX('YEAR_Cheatsheet';('YEAR_Cheatsheet'[Year]*100+'YEAR_Cheatsheet'[Weeknumber]);;ASC;Dense)

Ist/Soll% = ((([RüstenIstSUM] + [MaschIstSum])/([RüstenSollSUM] + [MaschSollSum])) -1)

The 4 measures are all SUMs from columns with decimal numbers.

ResultM =

(

    CALCULATE (

        [Ist/Soll%];

        FILTER ( ALL ( 'YEAR_Cheatsheet' ); 'YEAR_Cheatsheet'[Index2] = MIN ( 'YEAR_Cheatsheet'[Index2] ) + 1 )

    ) + [Ist/Soll%]

) / 2

Lastweek1 =

    CALCULATE (

        [Ist/Soll%];

        FILTER ( ALL ( 'YEAR_Cheatsheet' ); 'YEAR_Cheatsheet'[Index2] = MIN ( 'YEAR_Cheatsheet'[Index2] ) + 1 )

    )

Lastweek2 =

    CALCULATE (

        [Ist/Soll%];

        FILTER ( ALL ( 'YEAR_Cheatsheet' ); 'YEAR_Cheatsheet'[Index2] = MAX ( 'YEAR_Cheatsheet'[Index2] ) - 1 )

    )