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