Accumulated count of the last 07 days

[vinicius_ramos]

Hello friends, Can someone help me?

I need to do a count logs number in the last 07 days, how can i do this in power query? Every day my base is updated.

In my example, i need to count de collumn conteudo in the last 07 days.

how can i do this?

Thanks a lot !

[vinicius_ramos]

Hello, @Vera_33

Here is other print:

I want do count the collunm conteudo, in the last 07 days. For Example: 

In short, i have this database:

Date	Conteudo
2021-07-15	A
2021-07-16	B
2021-07-17	C
2021-07-18	D
2021-07-19	E
2021-07-20	F
2021-07-21	G
2021-07-22	H
2021-07-23	I
2021-07-24	J
2021-07-25	K
2021-07-26	L
2021-07-27	M

 And i need count the cells that are not blank in the last 07 days, just count from the 20th onwards. How i can do this?

Thanks a lot!

[Vera_33]

Hi @vinicius_ramos 

You want to get the Count in Power Query? I feel like you need it in a visual with a DAX measure...and if you have the same value in the column, you need to count all rows or distinct?

Here is one way in M, do not count distinct value, if you need, go List.Distinct that column

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc63DcAwDATAXVgLEEUH2aVznkDg/mvY8DffXnWlSGqi5mhqSYIM4uGjlmgEZaIJ1BHNoJ5o+cmUaAUlog1kRDuoIjpANdEJ4v0F4v0N4v0j7i8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Conteudo = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Conteudo", type text}}),
    #"Filtered Rows" = Table.RowCount( Table.SelectRows(#"Changed Type", each Date.IsInPreviousNDays([Date], 7) and [Conteudo]<>""))
in
    #"Filtered Rows"

But I really think you need DAX...and you need to consider if you need Distinct, and your Date will be greater than today or not

test = COUNTROWS(FILTER(yourTable,yourTable[Date]>=TODAY()-7&&yourTable[Conteudo]<>BLANK()))

[vinicius_ramos]

Hi, @Vera_33 

I dont need count distinct, i need count all. The exemple that you give to me, its all right. Now i have another challenge. 

Name	Date	Conteudo
1	2021-07-15	A
1	2021-07-16	B
1	2021-07-17	C
1	2021-07-18	D
1	2021-07-19	E
1	2021-07-20	F
1	2021-07-21	G
1	2021-07-22	H
1	2021-07-23	I
1	2021-07-24	J
1	2021-07-25	K
1	2021-07-26	L
1	2021-07-27	M
2	2021-07-15	A
2	2021-07-16	B
2	2021-07-17	C
2	2021-07-18	D
2	2021-07-19	E
2	2021-07-20	F
2	2021-07-21	G
2	2021-07-22	H
2	2021-07-23	I
2	2021-07-24	J
2	2021-07-25	K
2	2021-07-26	L
2	2021-07-27	M
2	2021-07-27	N
2	2021-07-27	O
2	2021-07-27	P

I need a validation, that bring to me the count of the last 07 days per item in the "name" column, if it is greater than 07 = "Problem", else "OK".

Note that he should check this every date, I can do a collunm if this validation?

Thanks !

[v-kelly-msft · Accepted solution]

Hi  @vinicius_ramos ,

Using below M codes:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdA9DoJAGIThu3w1CewooiWgovjbk73/NQwms04xsZs83bzLEimq6OrU1miw7j5yVXRHHVQ76qi6px5VD9STKBrqWTVRJ1VQL6ob6lV1S51Vy7ebavl2Vy3fHl+FrQNbB7YObB3YOrB1YOvA1oGtA1sHtg5sHfyp89On1ZfVd+T8AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Date = _t, Conteudo = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", Int64.Type}, {"Date", type date}, {"Conteudo", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Duration.Days(Date.From(DateTime.LocalNow())-[Date])<=7 then "OK" else "Problem")
in
    #"Added Custom"

And you will see that once the date is over 7 days since now,you will get problem,else ok.

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Syndicate_Admin]

Hi @vinicius_ramos 

So if you check today 2021-07-27, you count from 2021-07-20, do you count today as well? If not, all ok for your sample. You can modify accordingly

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddBNDoJAGIPhu3xrEmaqiC4BRcXfPZn7X8NIjK1JXfbZ9Z3nyFFFburU1kh4jy5K9eGNcE9uhQfyVnhP3gkfvowkPJKz8JEM4RN5JXwmr4Unsr68kPXllawvbwvDp4JPBZ8KPhV8KvhU8KngU8Gngk8Fnwo+Ff6n+uG754fnZ5TyAg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Date = _t, Conteudo = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", Int64.Type}, {"Date", type date}, {"Conteudo", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Name"}, #"Changed Type", {"Name"}, "all", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom", each [alltable=[all],
start=Date.AddDays([Date],-7),
end=[Date],
result= 
if Table.RowCount( Table.SelectRows(alltable, each ([Date]>= start and [Date] < end and [Conteudo]<>"")))>7 then "Problem" else "OK"  ][result])
in
    #"Added Custom"

[Vera_33]

Hi @vinicius_ramos 

So if you check today 2021-07-27, you count from 2021-07-20, do you count today as well? If not, all ok for your sample. You can modify accordingly

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddBNDoJAGIPhu3xrEmaqiC4BRcXfPZn7X8NIjK1JXfbZ9Z3nyFFFburU1kh4jy5K9eGNcE9uhQfyVnhP3gkfvowkPJKz8JEM4RN5JXwmr4Unsr68kPXllawvbwvDp4JPBZ8KPhV8KvhU8KngU8Gngk8Fnwo+Ff6n+uG754fnZ5TyAg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Date = _t, Conteudo = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", Int64.Type}, {"Date", type date}, {"Conteudo", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Name"}, #"Changed Type", {"Name"}, "all", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom", each [alltable=[all],
start=Date.AddDays([Date],-7),
end=[Date],
result= 
if Table.RowCount( Table.SelectRows(alltable, each ([Date]>= start and [Date] < end and [Conteudo]<>"")))>7 then "Problem" else "OK"  ][result])
in
    #"Added Custom"

[Vera_33]

Hi @vinicius_ramos 

I want to help but I can't see your pic clearly. Would you provide some sample data and expected result in a format which people can copy?

[vinicius_ramos]

No, maybe I'm having a difficulty making you understand what I need.

[Vera_33]

Hi @vinicius_ramos 

Can you explain what you want? Maybe go draw some data in Excel to present the result you want?

[v-kelly-msft]

Hi @vinicius_ramos ,

Have you checked my reply?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[vinicius_ramos]

@v-kelly-msft 

Hello, yes i check.

But is not what i want.

I try to explain again

[v-kelly-msft]

Hi @vinicius_ramos ，

OK,waiting for your update.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[vinicius_ramos]

@v-kelly-msft 

Hello, 

I will explain my real situation, i have this base assuming today is the 4th of july:

Data	praza	domicilio	tv	id_evento	conteudo
01/07/2021	110	1	0	7169	DataBaseManager
01/07/2021	110	980	0	7169	RestartManager
01/07/2021	110	980	0	7169	resolvetopo
02/07/2021	110	981	0	7169	RestartManager
03/07/2021	110	983	0	7169	RestartManager
01/07/2021	110	983	0	7169	gerenciadorpacotes485
01/07/2021	110	10668	0	7169	GeradorRegistrosDIB6
02/07/2021	110	10668	0	7169	gerenciadorpacotes485
01/07/2021	110	10700	0	7169	meter2net
02/07/2021	110	10700	0	7169	gerenciadorfgp
02/07/2021	110	10700	0	7169	agc
03/07/2021	110	10700	0	7169	identificador_perifericos
03/07/2021	110	10700	0	7169	sampler2
01/07/2021	110	10714	1	7169	resolvetopo
01/07/2021	110	10716	0	7169	meter2net
03/07/2021	110	10716	0	7169	meter2net
02/07/2021	110	10716	0	7169	meter2net
01/07/2021	110	10751	0	7169	meter2net
01/07/2021	110	10751	0	7169	meter2net
01/07/2021	110	10751	0	7169	meter2net

The collunm "conteudo" represents which the application reset and collunm "domicilio" represents the place. I need to count how many resets were done in the last 03 days and if there were more than 03 resets in the last 03 days = "Problem" if less than 03 resets = "OK".

I choose one domicilio to example:

domicilio	numbers of reset  in the last 03 days ( count "conteudo")	validation
10700	5	Problem

I need a column to do this process and return me if there is a problem or OK.

Can you help me?

thank you for the patience