Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Solved! Go to Solution.
OK, I've done it.
Here's the M script:
let Source = <A reference to your table here>, #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Number", Int64.Type}, {"Name", type text}}), #"Buffered Table" = Table.Buffer(#"Changed Type"), #"Add Day 28 Days Back" = Table.AddColumn(#"Changed Type", "Date28DaysBack", each [Date] - #duration(27, 0, 0, 0)), #"Add Count of Name (28 Days Back)" = Table.AddColumn( #"Add Day 28 Days Back", "Count Of Name (28 Days Back)", (r) => let A = Table.SelectRows(#"Buffered Table", each [Name] = r[Name] and r[Date28DaysBack] <= [Date] and [Date] <= r[Date]), CountOfRows = Table.RowCount(A) in CountOfRows ), #"Removed Auxiliary Column (28)" = Table.RemoveColumns(#"Add Count of Name (28 Days Back)",{"Date28DaysBack"}), #"Add Day 30 Days Back" = Table.AddColumn(#"Removed Auxiliary Column (28)", "Date30DaysBack", each [Date] - #duration(29, 0, 0, 0)), #"Add Count of Combination (30 Days Back)" = Table.AddColumn( #"Add Day 30 Days Back", "Count Of Combination (30 Days Back)", (r) => let A = Table.SelectRows(#"Buffered Table", each [Number] = r[Number] and [Name] = r[Name] and r[Date30DaysBack] <= [Date] and [Date] <= r[Date]), CountOfRows = Table.RowCount(A) in CountOfRows ), #"Removed Auxiliary Column (30)" = Table.RemoveColumns(#"Add Count of Combination (30 Days Back)",{"Date30DaysBack"}) in #"Removed Auxiliary Column (30)"
And here's what my Source table looks like:
Best
Darek
OK, I've done it.
Here's the M script:
let Source = <A reference to your table here>, #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Number", Int64.Type}, {"Name", type text}}), #"Buffered Table" = Table.Buffer(#"Changed Type"), #"Add Day 28 Days Back" = Table.AddColumn(#"Changed Type", "Date28DaysBack", each [Date] - #duration(27, 0, 0, 0)), #"Add Count of Name (28 Days Back)" = Table.AddColumn( #"Add Day 28 Days Back", "Count Of Name (28 Days Back)", (r) => let A = Table.SelectRows(#"Buffered Table", each [Name] = r[Name] and r[Date28DaysBack] <= [Date] and [Date] <= r[Date]), CountOfRows = Table.RowCount(A) in CountOfRows ), #"Removed Auxiliary Column (28)" = Table.RemoveColumns(#"Add Count of Name (28 Days Back)",{"Date28DaysBack"}), #"Add Day 30 Days Back" = Table.AddColumn(#"Removed Auxiliary Column (28)", "Date30DaysBack", each [Date] - #duration(29, 0, 0, 0)), #"Add Count of Combination (30 Days Back)" = Table.AddColumn( #"Add Day 30 Days Back", "Count Of Combination (30 Days Back)", (r) => let A = Table.SelectRows(#"Buffered Table", each [Number] = r[Number] and [Name] = r[Name] and r[Date30DaysBack] <= [Date] and [Date] <= r[Date]), CountOfRows = Table.RowCount(A) in CountOfRows ), #"Removed Auxiliary Column (30)" = Table.RemoveColumns(#"Add Count of Combination (30 Days Back)",{"Date30DaysBack"}) in #"Removed Auxiliary Column (30)"
And here's what my Source table looks like:
Best
Darek
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |