Forum Discussion
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])))
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"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
