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 individual clients of each month by 1%?

 

ProductJanFebMarAprMayJunJulAugSepOctNovDecTotal
Product 1  1,000.00     990.00  980.10  970.30  960.60  950.99  941.48  932.07  922.74  913.52  904.38  895.34  11,361.51
Product 1   1,000.00  990.00  980.10  970.30  960.60  950.99  941.48  932.07  922.74  913.52  904.38  10,466.17

 

There is no data for July to December, yet the said measure should calculate for the entire year.

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.

9 Replies

  • 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.

    • Atif's avatar
      Atif
      Resolver I
      6 years ago

      Thank you, Ashish_Mathur!

       

      Please find "raw data", "table with ACTUAL numbers", and "the DESIRED Result".

       

      https://1drv.ms/x/s!AjrCK_FQzChZho5oOvwX2jiSMjcIaw?e=0lLfcC

       

      Please note that I used the following measure, but it was only with a fixed value of the first month.

       

      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
      )

       

      Best,

       

      Atif

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

    Hi Atif ,

     

    How about the result after you follow the suggestions mentioned in my original post?

    Could you please provide more details or expected result about it If it doesn't meet your requirement?

    If you've fixed the issue on your own please kindly share your solution.

     

    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.