cancel
Showing results for
Did you mean:
Regular Visitor

## Target based on last promotion/startdate

Hi All,

I've the following issue/question: I created a dashboard with target and results of sales employees. Each profession level has it own target. When an employee gets promoted, the target changes. So starting from that date, I wanna show a different target. Also I want take the startdate into account.

I now have two tables, on with the changes in profession level, where the first entry per person is also startdate:

 Employee Job Title Date John Sales 1-9-2017 Emma Sales 1-8-2016 Dave Sales 1-10-2017 Emma Senior Sales 1-2-2018 John resignation 1-3-2018

And one the the targets:

 Job Title Monthly Target Sales 2 Senior Sales 3

And besides these a calendar table and some other non relevant table's.

With this data, I want to show that Emma has a target of 2 per month, starting on 1-8-2016, until she is promoted at 1-2-2018, than it changes to 3 per month. John has a target of 2, starting on 1-9-2017 untill he resigns at 1-3-2018 and Dave has a target of 2 starting at 1-10-2017 which is still the same.

Does anyone know if this is posible and what the DAX formula(s) is/are?

1 ACCEPTED SOLUTION

Accepted Solutions
Regular Visitor

## Re: Target based on last promotion/startdate

Found a solution. The Dax was incredibly simple when I started working with a date from and a date till value.  See formula at the bottom.

@danextian your idea and M query helped me a lot! I used your method of calculating the date difference, but I used it for finding the next promotion date and subtract 1 day to create a Date Till column. This way i could use a list employees and their promotion date to generate the periods.

Thanks for the replies!

```Target pm = CALCULATE(SUMX(Target;[Monthly Target])*DISTINCTCOUNT(Calander[Year - Month]);
FILTER(Promtions;[Date from]<=STARTOFMONTH(Calander[Date].[Date]));
FILTER(Promtions;[Date till]>STARTOFMONTH(Calander[Date].[Date])))```

4 REPLIES 4
New Contributor

## Re: Target based on last promotion/startdate

I'm not sure how this would be done in DAX but this would be simple in M.

The goal is to generate a list of dates where a particular target is valid based on the old and new dates. Try this:

```let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUQpOzEktBtKGupa6RgaG5kqxOtFKrrm5iShyFiA5M7CcS2JZKoqcoQGmxtS8zPwiBYQaI5ASC7ASqL1FqcWZ6XmJJZn5eWAVxlAVsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Employee = _t, #"Job Title" = _t, #"Date of Change in Target" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Job Title", type text}, {"Date of Change in Target", type date}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Employee", Order.Ascending}, {"Date of Change in Target", Order.Ascending}}),
//needed to be able to reference previous or next row
/*get the difference between the dates between the current and next rows
returns zero if the employee names between the rows do not match */
if [Employee]=#"Sorted Rows"[Employee]{[Index]+1} then  Number.From(#"Sorted Rows"[Date of Change in Target]{[Index]+1}- [Date of Change in Target]) else 0
otherwise 0, Int64.Type),
//returns a list of dates
#"Added Custom1" = Table.AddColumn(#"Added Custom", "List of Dates", each List.Dates([Date of Change in Target], [DateDifference], #duration(1,0,0,0)), type list),
//convert the list to rows
#"Expanded List of Dates" = Table.ExpandListColumn(#"Added Custom1", "List of Dates"),
#"Added Custom2" = Table.AddColumn(#"Expanded List of Dates", "Date Target is Valid", each if [List of Dates] = null then [Date of Change in Target] else [List of Dates], type date),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Monthly Target", each if [Job Title]="Sales" then 2 else if [Job Title]="Senior Sales" then 3 else null, Int64.Type),
#"Filled Down" = Table.FillDown(#"Added Custom3",{"Monthly Target"}),
#"Removed Other Columns" = Table.SelectColumns(#"Filled Down",{"Employee", "Job Title", "Date of Change in Target", "Date Target is Valid", "Monthly Target"})
in
#"Removed Other Columns"```
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Regular Visitor

## Re: Target based on last promotion/startdate

Hey @danextian, thanks for the tip! It does the trick for the data I provided, but the actual data is somewhat more complex which provides me with a lot of new problems (a.o. many to many, converting to monthly/daily targets, sum of team members, target in the period after the promotion).

So I'm still hoping for a DAX solution that would save me the time / effort of rebuilding half of my data/report.

Super Contributor

## Re: Target based on last promotion/startdate

@The_Coon you may be able to use the earlier function... however i could be wrong

here is an example of how it works

https://msdn.microsoft.com/en-us/query-bi/dax/earlier-function-dax

http://tinylizard.com/dax-earlier-function/

Proud a to be a Datanaut!
Regular Visitor

## Re: Target based on last promotion/startdate

Found a solution. The Dax was incredibly simple when I started working with a date from and a date till value.  See formula at the bottom.

@danextian your idea and M query helped me a lot! I used your method of calculating the date difference, but I used it for finding the next promotion date and subtract 1 day to create a Date Till column. This way i could use a list employees and their promotion date to generate the periods.

Thanks for the replies!

```Target pm = CALCULATE(SUMX(Target;[Monthly Target])*DISTINCTCOUNT(Calander[Year - Month]);
FILTER(Promtions;[Date from]<=STARTOFMONTH(Calander[Date].[Date]));
FILTER(Promtions;[Date till]>STARTOFMONTH(Calander[Date].[Date])))```

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

#### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (5,215)