Crossjoin with filters

[TheProv]

Hi everyone.
So, I have a table that defines subscriptions periods for users, that is like that:

id	user_id	ends_at	created_at
1	1		18/12/2022
2	2	22/12/2022	19/12/2022
3	3		22/12/2022

What I'm trying to obtain is a crossjoin between my dates table and the user_id table, but only for that in which that user was active, or Dates[date] has to be bigger after created_at and before ends_at if ends_at is not blank. If ends_at is blank it needs to go up to today.

What I'm trying to achieve is something like this:

user_id	date
1	18/12/2022
1	19/12/2022
1	20/12/2022
1	21/12/2022
1	22/12/2022
1	23/12/2022
2	19/12/2022
2	20/12/2022
2	21/12/2022
2	22/12/2022
3	22/12/2022
3	23/12/2022

I understand that I need to do a crossjoin with filters, but I didn't find any relatable solution so far.

[TheProv]

This looks exactly like what I was trying to achieve! Worked like a charm. Thanks!

[AlB]

Hi @TheProv 

I wold do this in PQ. Place the following M code in a blank query to see the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQIiQwt9QyN9IwMjI6VYnWglI6CQkRFcCChviSJvDNGFpCQ2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [user_id = _t, ends_at = _t, created_at = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"user_id", Int64.Type}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"ends_at", type date}, {"created_at", type date}}, "en-GB"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Date", each let 
last_= if [ends_at] = null then Date.From(DateTime.LocalNow()) else [ends_at],
len_ = Duration.Days(last_ - [created_at]) + 1,
res_ =
List.Dates([created_at], len_, #duration(1, 0, 0, 0))
in 
res_),
    #"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date")
in
    #"Expanded Date"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[FreemanZ]

hi @TheProv 

Apparently Jihwan's solution works. It is also advisible to process the data in Power Query, the code looks like this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"ends_at", type date}, {"created_at", type date}}, "en-150"),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type with Locale",null,#date(2022, 12, 23),Replacer.ReplaceValue,{"ends_at", "created_at"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Replaced Value",{"id"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns1", "Custom", each [ends_at]-[created_at]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"ends_at"}),
    #"Added Custom4" = Table.AddColumn(#"Removed Columns", "Custom.1", each [Custom]+1),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom4",{"Custom"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns2", "Custom", each {0..[Custom.1]-1}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom1", "Custom"),
    #"Removed Columns3" = Table.RemoveColumns(#"Expanded Custom",{"Custom.1"}),
    #"Added Custom2" = Table.AddColumn(#"Removed Columns3", "Custom.1", each Date.AddDays([created_at],[Custom])),
    #"Removed Columns4" = Table.RemoveColumns(#"Added Custom2",{"created_at", "Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns4",{{"Custom.1", "date"}})
in
    #"Renamed Columns"

it lands the same result:

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new table.

New table = 
SUMMARIZE (
    GENERATE (
        Data,
        FILTER (
            'Calendar',
            'Calendar'[Date] >= Data[created_at]
                && 'Calendar'[Date]
                    <= IF ( Data[ends_at] <> BLANK (), Data[ends_at], TODAY () )
        )
    ),
    Data[user_id],
    'Calendar'[Date]
)