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

P

pychin

New Member
Joined
Jun 5, 2019
Messages
8
[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
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Name[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl66, width: 64"]1/5/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"]kent[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl66, width: 75"]2,000.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]1/5/2019[/TD]
[TD][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"]ABBY[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl66, width: 75"]1,000.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]1/5/2019[/TD]
[TD][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"]HADYN[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl66, width: 75"]5,439.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]1/5/2019[/TD]
[TD][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"]MISSY[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl66, width: 75"]321.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]1/5/2019[/TD]
[TD][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"]TORI[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl66, width: 75"]-1,233.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]1/5/2019[/TD]
[TD][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"]Jackie[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl66, width: 75"]323.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl66, width: 64"]2/5/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"]kent[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl66, width: 75"]2,000.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl66, width: 64"]2/5/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"]ABBY[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl66, width: 75"]-2,315.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl66, width: 64"]2/5/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"]HADYN[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl66, width: 75"]8,282.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl66, width: 64"]3/5/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"]HADYN[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl66, width: 75"]1,222.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl66, width: 64"]3/5/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"]MISSY[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl66, width: 75"]3,212.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl66, width: 64"]3/5/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"]TORI[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl66, width: 75"]231.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl66, width: 64"]3/5/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"]kent[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl66, width: 75"]1,020.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl66, width: 64"]3/5/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"]ABBY[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl66, width: 75"]1,234.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl66, width: 64"]4/5/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"]kent[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl66, width: 75"]3,201.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl66, width: 64"]4/5/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"]ABBY[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl66, width: 75"]234.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl66, width: 64"]4/5/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"]HADYN[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl66, width: 75"]9,836.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl66, width: 64"]5/5/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"]kent[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl66, width: 75"]888.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl66, width: 64"]5/5/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"]ABBY[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl66, width: 75"]223.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl66, width: 64"]5/5/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"]HADYN[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl66, width: 75"]121.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl66, width: 64"]6/5/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"]HADYN[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl66, width: 75"]332.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl66, width: 64"]6/5/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"]kent[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl66, width: 75"]8,765.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl66, width: 64"]6/5/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"]ABBY[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl66, width: 75"]-1,222.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl66, width: 64"]7/5/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"]ABBY[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl66, width: 75"]123.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


i want the result to be like this
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Name[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 118"]
<tbody>[TR]
[TD="class: xl65, width: 118"]continuous 4days[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]kent[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]ABBY[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]HADYN[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]MISSY[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]TORI[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Jackie[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
Try that code
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
 
Upvote 0
pinarello said:
Try that code
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
Click to expand...


hi pinarello, thanks for your help...can you send me that sample power bi file?
 
Upvote 0
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
 
Upvote 0
pinarello said:
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
Click to expand...

now i can open already...thanks

this you make by power bi or excel power query?
 
Upvote 0
I used Power Query in Excel. But with only a few exceptions, Power Query should be identical in Excel and Power BI
 
Upvote 0
pinarello said:
I used Power Query in Excel. But with only a few exceptions, Power Query should be identical in Excel and Power BI
Click to expand...


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)
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
continuous exceed 4 days negative
Replies
0
Views
316
pychin
P

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top