Forum Discussion
Solved
Help expanding table according dates
I have a table like below, where a new record is created when there is a change in the status of a task. task stat lastupdate A 1 28/04/2022 A 3 01/05/2022 A 5 05/05/2022 B...
Dukezera Try:
Tasks2 = ADDCOLUMNS( GENERATE( DISTINCT('Tasks'[task]), CALENDAR(MIN('Tasks'[lastupdate]),MAX('Tasks'[lastupdate])) ), "status", VAR __Date = [Date] VAR __Task = [task] VAR __TargetDate = MAXX(FILTER('Tasks',[lastupdate] <= __Date && [task] = __Task),[lastupdate]) RETURN MAXX(FILTER('Tasks',[task] = __Task && [lastupdate] = __TargetDate),[stat]) )Dukezera Great! So I don't know where those columns are coming from exactly or how they work but seems like you could do something like:
Tasks2 = VAR __Table = ADDCOLUMNS( GENERATE( DISTINCT('Tasks'[task]), CALENDAR(MIN('Tasks'[lastupdate]),MAX('Tasks'[lastupdate])) ), "status", VAR __Date = [Date] VAR __Task = [task] VAR __TargetDate = MAXX(FILTER('Tasks',[lastupdate] <= __Date && [task] = __Task),[lastupdate]) RETURN MAXX(FILTER('Tasks',[task] = __Task && [lastupdate] = __TargetDate),[stat]), "approved", VAR __Date = [Date] VAR __Task = [task] VAR __TargetDate = MAXX(FILTER('Tasks',[lastupdate] <= __Date && [task] = __Task),[lastupdate]) RETURN MAXX(FILTER('Tasks',[task] = __Task && [lastupdate] = __TargetDate),[approved]), "stn", VAR __Date = [Date] VAR __Task = [task] VAR __TargetDate = MAXX(FILTER('Tasks',[lastupdate] <= __Date && [task] = __Task),[lastupdate]) RETURN MAXX(FILTER('Tasks',[task] = __Task && [lastupdate] = __TargetDate),[stn]) ) RETURN FILTER(__Table, [status] <> BLANK() && [approved] <> BLANK() && [stn] <> BLANK())
Dukezera Try:
Tasks2 =
ADDCOLUMNS(
GENERATE(
DISTINCT('Tasks'[task]),
CALENDAR(MIN('Tasks'[lastupdate]),MAX('Tasks'[lastupdate]))
),
"status",
VAR __Date = [Date]
VAR __Task = [task]
VAR __TargetDate = MAXX(FILTER('Tasks',[lastupdate] <= __Date && [task] = __Task),[lastupdate])
RETURN
MAXX(FILTER('Tasks',[task] = __Task && [lastupdate] = __TargetDate),[stat])
)I tried but got this error:
The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.Dukezera What I provided is a calculated table formula.
Greg_Deckler works perfectly!!!!!!!
What do I need to do with I have a table like this (with two more column and works like status) and also how to remove rows with blank values in any of this columns (status, approved or stn)?:
task status lastupdate approved stn A 1 28/04/2022 N N31 A 3 01/05/2022 N N31 A 5 05/05/2022 Y N31 B 1 28/04/2022 N N31 B 3 03/05/2022 Y N31 B 4 05/05/2022 Y N31
