Power Bi how to meke continuous exceed 4 days (positive), will automatic show total Positive days

[pychin]

[FONT=&quot]Hi all[/FONT]

[FONT=&quot]At Power Bi how to make it?[/FONT]

[FONT=&quot]If the customer have positive for continuous 4 days, will automatic show how many days.No matter on which day, as long as continuous 4 days.[/FONT]

[FONT=&quot]example 1: kent continuous already exceed 4 days (positive) , he total got 6 days (positive). then automatic show me 6 days...[/FONT]

[FONT=&quot]example 2: ABBY total got 5 days (positive), but she is not continuous 5 days (positive), so just show me 0[/FONT]

[FONT=&quot]Thanks[/FONT]

sample:

this is data

Date	Name	Value
1/5/2019 <tbody> </tbody>	kent <tbody> </tbody>	2,000.00 <tbody> </tbody>
1/5/2019	ABBY <tbody> </tbody>	1,000.00 <tbody> </tbody>
1/5/2019	HADYN <tbody> </tbody>	5,439.00 <tbody> </tbody>
1/5/2019	MISSY <tbody> </tbody>	321.00 <tbody> </tbody>
1/5/2019	TORI <tbody> </tbody>	-1,233.00 <tbody> </tbody>
1/5/2019	Jackie <tbody> </tbody>	323.00 <tbody> </tbody>
2/5/2019 <tbody> </tbody>	kent <tbody> </tbody>	2,000.00 <tbody> </tbody>
2/5/2019 <tbody> </tbody>	ABBY <tbody> </tbody>	-2,315.00 <tbody> </tbody>
2/5/2019 <tbody> </tbody>	HADYN <tbody> </tbody>	8,282.00 <tbody> </tbody>
3/5/2019 <tbody> </tbody>	HADYN <tbody> </tbody>	1,222.00 <tbody> </tbody>
3/5/2019 <tbody> </tbody>	MISSY <tbody> </tbody>	3,212.00 <tbody> </tbody>
3/5/2019 <tbody> </tbody>	TORI <tbody> </tbody>	231.00 <tbody> </tbody>
3/5/2019 <tbody> </tbody>	kent <tbody> </tbody>	1,020.00 <tbody> </tbody>
3/5/2019 <tbody> </tbody>	ABBY <tbody> </tbody>	1,234.00 <tbody> </tbody>
4/5/2019 <tbody> </tbody>	kent <tbody> </tbody>	3,201.00 <tbody> </tbody>
4/5/2019 <tbody> </tbody>	ABBY <tbody> </tbody>	234.00 <tbody> </tbody>
4/5/2019 <tbody> </tbody>	HADYN <tbody> </tbody>	9,836.00 <tbody> </tbody>
5/5/2019 <tbody> </tbody>	kent <tbody> </tbody>	888.00 <tbody> </tbody>
5/5/2019 <tbody> </tbody>	ABBY <tbody> </tbody>	223.00 <tbody> </tbody>
5/5/2019 <tbody> </tbody>	HADYN <tbody> </tbody>	121.00 <tbody> </tbody>
6/5/2019 <tbody> </tbody>	HADYN <tbody> </tbody>	332.00 <tbody> </tbody>
6/5/2019 <tbody> </tbody>	kent <tbody> </tbody>	8,765.00 <tbody> </tbody>
6/5/2019 <tbody> </tbody>	ABBY <tbody> </tbody>	-1,222.00 <tbody> </tbody>
7/5/2019 <tbody> </tbody>	ABBY <tbody> </tbody>	123.00 <tbody> </tbody>

<tbody>
</tbody>

i want the result to be like this

Name <tbody> </tbody>	continuous 4days <tbody> </tbody>
kent <tbody> </tbody>	6
ABBY <tbody> </tbody>	0
HADYN <tbody> </tbody>	6
MISSY <tbody> </tbody>	0
TORI <tbody> </tbody>	0
Jackie <tbody> </tbody>	0

<tbody>
</tbody>

 

[pinarello]

Try that code

let    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    Changed_type = Table.Buffer(Table.TransformColumnTypes(Source,{{"Date", type date}})),
    Sorted_lines = Table.Sort(Changed_type,{{"Name", Order.Ascending}, {"Date", Order.Descending}}),
    added_Idx = Table.AddIndexColumn(Sorted_lines, "Index", 0, 1),
    Userdefined_Col = Table.AddColumn(added_Idx, "hlp", 
     each if   [Amount] < 0 then 0 else
          if   [Index] = 0 or [Name] <> added_Idx[Name]{[Index]-1} 
          then 1 
          else null),
    Fill_down = Table.FillDown(Userdefined_Col,{"hlp"}),
    Filtered_Lines = Table.SelectRows(Fill_down, each ([hlp] = 1)),
    FGrouped_Lines = Table.Group(Filtered_Lines, {"Name"}, {{"Anzahl", each Table.RowCount(_), type number}}),
    Replace_value = Table.ReplaceValue(FGrouped_Lines,1,0,Replacer.ReplaceValue,{"Anzahl"}),
    Replace_value_2 = Table.ReplaceValue(Replace_value,2,0,Replacer.ReplaceValue,{"Anzahl"}),
    Replaced_value_3 = Table.ReplaceValue(Replace_value_2,3,0,Replacer.ReplaceValue,{"Anzahl"}),
    Renamed_columns = Table.RenameColumns(Replaced_value_3,{{"Anzahl", "Continuous 4days"}}),
    Sorted_lines_2 = Table.Sort(Renamed_columns,{{"Name", Order.Ascending}})
in
    Sorted_lines_2

 

[pychin]

Try that code

let    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    Changed_type = Table.Buffer(Table.TransformColumnTypes(Source,{{"Date", type date}})),
    Sorted_lines = Table.Sort(Changed_type,{{"Name", Order.Ascending}, {"Date", Order.Descending}}),
    added_Idx = Table.AddIndexColumn(Sorted_lines, "Index", 0, 1),
    Userdefined_Col = Table.AddColumn(added_Idx, "hlp", 
     each if   [Amount] < 0 then 0 else
          if   [Index] = 0 or [Name] <> added_Idx[Name]{[Index]-1} 
          then 1 
          else null),
    Fill_down = Table.FillDown(Userdefined_Col,{"hlp"}),
    Filtered_Lines = Table.SelectRows(Fill_down, each ([hlp] = 1)),
    FGrouped_Lines = Table.Group(Filtered_Lines, {"Name"}, {{"Anzahl", each Table.RowCount(_), type number}}),
    Replace_value = Table.ReplaceValue(FGrouped_Lines,1,0,Replacer.ReplaceValue,{"Anzahl"}),
    Replace_value_2 = Table.ReplaceValue(Replace_value,2,0,Replacer.ReplaceValue,{"Anzahl"}),
    Replaced_value_3 = Table.ReplaceValue(Replace_value_2,3,0,Replacer.ReplaceValue,{"Anzahl"}),
    Renamed_columns = Table.RenameColumns(Replaced_value_3,{{"Anzahl", "Continuous 4days"}}),
    Sorted_lines_2 = Table.Sort(Renamed_columns,{{"Name", Order.Ascending}})
in
    Sorted_lines_2

hi pinarello, thanks for your help...can you send me that sample power bi file?

 

[pinarello]

When re-testing, I noticed that the query did not return correct results in all cases. After the modification of the query, it should be correct now

Link to my OneDrive file

 

[pychin]

When re-testing, I noticed that the query did not return correct results in all cases. After the modification of the query, it should be correct now

Link to my OneDrive file

why i can't open it?

when i download and open, there show me
Excel PQ - Power Bi how to meke continuous exceed 4 days (positive) is corrupt an invalid report file

 

[pinarello]

I have now tested the link from my company laptop and was able to open, download and edit the folder without any problems.


In another thread sandy666 could probably open my folder, which I had created with my German Excel version (Office 365

 

[pychin]

I have now tested the link from my company laptop and was able to open, download and edit the folder without any problems.


In another thread sandy666 could probably open my folder, which I had created with my German Excel version (Office 365

now i can open already...thanks

this you make by power bi or excel power query?

 

[pinarello]

I used Power Query in Excel. But with only a few exceptions, Power Query should be identical in Excel and Power BI

 

[pychin]

I used Power Query in Excel. But with only a few exceptions, Power Query should be identical in Excel and Power BI

when i key this code got error...

if [Value] < 0 then 0 else if [Index] = 0 or [Name] <> added_Idx[Name]{[Index]-1} or [Value] > 0 then 1 else null

show me (Expression.Error: The name 'added_Idx' wasn't recognized. Make sure it's spelled correctly)

 

[pinarello]

Have now downloaded and installed Power BI. Afterwards created in Power BI a request with idential code of the Excel file. It works without any problems

have here the Power BI link