Help expanding table according dates

[Dukezera]

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	1	28/04/2022
B	3	03/05/2022
B	4	05/05/2022

The problem is that I need to plot a graph within a time range, where I know the status of each item regardless of the date it was changed/created. With that, I think the easiest is to transform to the table below:

task	status	lastupdate
A	1	28/04/2022
A	1	29/04/2022
A	1	28/04/2022
A	1	29/04/2022
A	1	30/04/2022
A	3	01/05/2022
A	3	02/05/2022
A	3	03/05/2022
A	3	04/05/2022
A	5	05/05/2022
B	1	28/04/2022
B	1	29/04/2022
B	1	30/04/2022
B	1	01/05/2022
B	1	02/05/2022
B	3	03/05/2022
B	3	04/05/2022
B	4	05/05/2022

However, I can't think of a way to do it, either directly in Power BI or even in SQL, since I'm connecting to a redshift database through a sql query. Could you please help me? Thank you

[Greg_Deckler · Accepted solution]

@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])
    )

[Greg_Deckler · Accepted solution]

@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]

@Greg_Deckler 

I tried but got this error:
The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

[Greg_Deckler]

@Dukezera What I provided is a calculated table formula.

[Dukezera]

@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

[Greg_Deckler · Accepted solution]

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