cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
The_Coon Regular Visitor
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:

EmployeeJob TitleDate
JohnSales1-9-2017
EmmaSales1-8-2016
DaveSales1-10-2017
EmmaSenior Sales1-2-2018
Johnresignation1-3-2018

 

And one the the targets:

Job TitleMonthly Target
Sales2
Senior Sales3

 

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
The_Coon Regular Visitor
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])))

 

View solution in original post

4 REPLIES 4
danextian New Contributor
New Contributor

Re: Target based on last promotion/startdate

Hi @The_Coon

 

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"
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
The_Coon Regular Visitor
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.

 

Nevertheless, thanks for your response!

 

vanessafvg Super Contributor
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/


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
The_Coon Regular Visitor
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])))

 

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

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

Microsoft Implementation for Communities Wins Award

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

Power Platform World Tour

Find out where you can attend!

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