Forum Discussion
Measure to decrease ACTUAL value for all preceding months
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.
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.
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.