Forum Discussion

TheProv's avatar
TheProv
Frequent Visitor
3 years ago
Solved

Crossjoin with filters

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 ...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    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]
)
FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

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: