Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Otto_Luvpuppy
Helper II
Helper II

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

Download this example 

 

Example 

View solution in original post

5 REPLIES 5
speedramps
Super User
Super User

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

Download this example 

 

Example 

amitchandak
Super User
Super User

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

Thanks...I will give that a try.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.