cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

28 days rolling count of repeats in power bi Calculated column

Please help!
 
I am trying to figure out how to calculate a rolling count in power bi calculated column.
 
For example count the number of each "Name" occurence in the last 28 days.
 
Also, another calculated column that count the number of occurrence of same "Name" and "Number" over the last 30 days.
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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:

Table with Names.PNG

Best

Darek

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

This is not a job for DAX. It's a job for Power Query. DAX should only be used for calculated columns if there is NO OTHER OPTION.

Can you please use Power Query to do the above? And if not, why not?

Best
Darek

Thanks,
Any suggestion on how to do this in Power Query?
Anonymous
Not applicable

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:

Table with Names.PNG

Best

Darek

View solution in original post

Many thanks for your help Derek.
It worked on small data set but when I used the M code on a larger data set it kept crashing powerbi after taking forever to load the data. What do you advise?
I also found out that the code created a table on each row. Is it possible to create just the columns.
Thanks
Anonymous
Not applicable

Of course. But you have to give me some sample data. Then I'll give you the M code to do what you want.

Best
Darek

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.