Target based on last promotion/startdate

[Anonymous]

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? 

[Anonymous · Accepted solution]

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

[danextian]

Hi @Anonymous

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
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
    /*get the difference between the dates between the current and next rows
    returns zero if the employee names between the rows do not match */
    #"Added Custom" = Table.AddColumn(#"Added Index", "DateDifference", each try 
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),
    //add target
    #"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"

[Anonymous]

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.

Nevertheless, thanks for your response!

[vanessafvg]

@Anonymous 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/