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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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
319
pychin
P

Forum statistics

Threads
1,224,823
Messages
6,181,183
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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