Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Compounding values in a power BI measure

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!

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi Anonymous 

    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

5 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    hi Anonymous 

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        v-lili6-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        hi Anonymous 

        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
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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!