cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Responsive Resident
Responsive Resident

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

Download this example 

 

Example 

View solution in original post

5 REPLIES 5
Highlighted
Super User IV
Super User IV

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

@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...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Responsive Resident
Responsive Resident

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

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()

)

 

Highlighted
Responsive Resident
Responsive Resident

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

Download this example 

 

Example 

View solution in original post

Highlighted
Helper I
Helper I

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

Thanks...I will give that a try.

Highlighted
Helper I
Helper I

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

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

 

Thanks

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors