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.

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.

    • v-zhenbw-msft's avatar
      v-zhenbw-msft
      Community Support
      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.

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      6 years ago

      Hi,

      There is no file at that link.