Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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])))
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])))
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"
Proud to be a Super User!
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!
@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/
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |