Measure to decrease ACTUAL value for all preceding months

[Atif]

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%?

Product	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total
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.

[v-zhenbw-msft]

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.

[Ashish_Mathur]

Hi,

Share the raw data from where you built that table.

[Atif]

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]

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]

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]

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]

https://1drv.ms/u/s!AjrCK_FQzChZho5yCaX4XDErP2IRmw?e=aQ5hK2

Above is the link to .pbix file, dear @v-zhenbw-msft.

Please have a look at the excel file too to get to know the expected output. The size has been reduced.

[Ashish_Mathur]

Hi,

There is no file at that link.

[v-zhenbw-msft · Accepted solution]

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.