Creating a Measure based on another Measure and spread across a Time Frame

[Otto_Luvpuppy]

Hi...I'm even struggling to formulate the question to be honest.

I have a Measure that counts rows depending on the content of a specific cell e.g. Overdue, On Target, Not Required etc.
No problem...this Measure works.

Now I need to create a 'target' line across a number of time periods. For example we want to reduce the Overdue figure from Period 1 by 60% by Period 6. So if at P6 the figure was 100 then P6 we need it to be 40. If this was static the I don't think I would have and issue but it needs to be dynamic and reference the Measure called 'Overdue'....and steadily decrease over the 6 periods to the final figure. The step decrease per period is a nice round 10% so I thought create a new Measure that always references the P1 figure and decrease from there:

Overdue Target = IF('MINMAX Period'[Year Period]="20/21-P1",'MainTable'[Overdue],
IF('MINMAX Period'[Year Period]="20/21-P2",CALCULATE('MainTable'[Overdue]*0.9,'MINMAX Period'[Year Period]="20/21-P1"),
IF('MINMAX Period'[Year Period]="20/21-P3",CALCULATE('MainTable'[Overdue]*0.8,'MINMAX Period'[Year Period]="20/21-P1"),
etc.etc.
)
)
)

This is plainly rubbish...but I cannot figure it out.

Any help gratefully accepted.

Thanks

[speedramps]

Please consider this solution and leave kudos

Create a calendar table with a list of dates and a month offset column.

With +1 for next month, 0 for the current month, -1 for last month, -2 for the month before and and so on.

Build a one to many relationship between the calendar date and your table date.

Then create measure to calculate your target% factor that you can apply to your totals.

Target % =

VAR maxoffset = MAX('Calendar'[MonthOffset])

RETURN

 SWITCH (

 TRUE (),

maxoffset =-0,1,

maxoffset =-1,0.9,

maxoffset =-2,0.8,

maxoffset =-3,0.7,

maxoffset =-4,0.6,

maxoffset =-5,0.5,

maxoffset =-6,0.4,

BLANK()

)

[Otto_Luvpuppy]

That looks interesting.....I will give that a try.

Thanks

[speedramps · Accepted solution]

Download this example 

Example 

[amitchandak]

@Otto_Luvpuppy , can you create an incremental period then you can get last period like

This Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank])))
Last Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank])-1))

Refer the way it is done in Week

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

[Otto_Luvpuppy]

Thanks...I will give that a try.