Forum Discussion
Creating a Measure based on another Measure and spread across a Time Frame
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
5 Replies
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()
)
That looks interesting.....I will give that a try.
Thanks
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
Thanks...I will give that a try.
