Forum Discussion

Atif's avatar
Atif
Resolver I
6 years ago
Solved

Measure to decrease ACTUAL value for all preceding months

I have a table with multiple clients and multiple products with values against each item from January to June.   Is there a way (measure) to decrease the ACTUAL figure of individual products and in...
Need Help
Tips and Tricks
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    Hi Atif ,

     

    Sorry for that when we finish downloading your PBIX file, we cannot open your desire result in excel link.

     

     

    We find two measures for you based on the measure you provide, you can refer to which one is your Desire Result.

     

    Measure = 
VAR __initialValue =
    CALCULATE ( SUM ( Table1[Revenue] ) )
VAR __decreaseBy = 0.01
RETURN
    __initialValue
        * CALCULATE (
            PRODUCTX (
                VALUES ( 'Table1'[Month_number] ),
                IF ( 'Table1'[Month_number] = 1, 1, 1 - __decreaseBy )
            ),
            FILTER (
                ALLSELECTED ( Table1 ),
                Table1[Month_number] <= MAX ( Table1[Month_number] )
            )
        )

     

    Measure 2 = 
IF ( MAX('Table1'[Month_number]) = 1, 1, 1-0.01 ) * CALCULATE(SUM(Table1[Revenue]))

     

     In the screen shot we just filter Product 1.

     

     

    If it doesn't meet your requirement, could you please take a screenshot of desire result and put it in your PBIX file?

    BTW, pbix as attached.

     

    Best regards,

     

    Community Support Team _ zhenbw

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

v-zhenbw-msft's avatar
v-zhenbw-msft
Community Support
6 years ago

Hi Atif ,

 

If you want to reduce the value of each month by 1%, you can refer the following steps.

 

1. Select product column and unpivot other columns.

 

 

 

2. Then we need to add a custom column to calculate the value of each month by 1%,

 

 

3. At last we need to remove the value column and pivot custom column.

 

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

 

Best regards,

 

Community Support Team _ zhenbw

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

  • Atif's avatar
    Atif
    Resolver I
    6 years ago

    Dear v-zhenbw-msft,

     

    Thanks for the response. Unfortunately, the problem still exists.

     

    This is the measure I used for the fixed value.

     

    Decrease =
    VAR __month = MAX ( 'Month'[Month] )
    VAR __initialValue = 1000
    VAR __decreaseBy = .01 //percentage
    RETURN
    __initialValue *
    CALCULATE (
    PRODUCTX (
    VALUES ( 'Month'[Month] ),
    IF ( 'Month'[Month] = 1, 1, 1-__decreaseBy )
    ),
    'Month'[Month] <= __month
    )
     
    However, this won't work when we talk about decreasing by 1% the ACTUAL value of a particular month, be it Jan, Mar, or May.
     
    You can access the table with ACTUAL numbers and the DESIRED result @ https://1drv.ms/x/s!AjrCK_FQzChZho5oOvwX2jiSMjcIaw?e=0lLfcC
     
    Best,
     
    Atif
    • v-zhenbw-msft's avatar
      v-zhenbw-msft
      Community Support
      6 years ago

      Hi Atif ,

       

      Thanks for sharing your raw data. But unfortunately your file is too big to open.

       

       

      Could you simplify the data and re-share it?
      If it is convenient, could you please share the PBIX file based on your raw data? Please don't contain any Confidential Information or Real data in your reply.

       

      Best regards,

       

      Community Support Team _ zhenbw

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