Counting back until a change occurs

Craigc3814 · Oct 9, 2018

I am using Excel 2016 Power Query & Power Pivot. I am going to do my best to explain this but I do not know if I can.

I get a massive bill from a vendor each month that has about 350 different account numbers on it. The bill tells me our usage and our charge. It comes in a .txt file

I use PowerQuery to clean that up and load it to a Power Pivot table.

What I need to do is account number by account number look and see how many months it has been since a change to the usage row has changed. So if I have received 6 bills in a row for the same usage, I want a column that returns 6.

I can think of two ways to MAYBE pull it off but you guys are genius here so I figured I would check prior to attempting something awful.

My thought is

A I have a table dedicated to just the most recent bill. It brings in all of the data for the current month bill. I also have the table that compiles all bills as I mentioned earlier. I was thinking there was some way through PowerPivot I tell the current month table to look at the compilation table and return me the first month that DOES NOT match then I could formulate current month minus the bill month returned

B Through PQ Organize Everything in the Compilation table by Account Number then ZtoA on Bill Month. Create some type of index column, then modulo column and subtract the difference to return the 6 months.

Any thoughts would be a game changer for me, I know people like to see the data unfortunately I am not allowed to share it so I will Mimic 1 tables below

Compilation Table
Acct # Month Rate Code Usage Charge
001 4/1/18 129 800 $12,507
001 4/1/18 145 150 $129
001 5/1/18 129 1000 $15,329
001 6/1/18 129 1000 $15,329
001 7/1/18 129 1000 $15,329
002 4/1/18 129 1300 $18,793

So I would want a 3 returned for acct # 001 Rate 129

The Current Month table is the EXACT same format. Only difference is it does not compile bills it just shows this months information for each account

VBA Geek · Oct 10, 2018

Hello Craig,

I am not sure if I have understood correctly but here is how you could do it.
I would use just the compiled bills table ( including current month bills ) and a Date dimension table.

You can see below screenshots:

Then, you can build the below simple model:

Then I have built the below Pivot Table, where, by using the pivot table filter, I can see ( from the point of view of the date in the filter ) which was the previous date with a different rate:

The measure are used are simple:

CurrentRate = IF ( HASONEVALUE ( Charges[Acct #] ), MAX ( Charges[Rate] ) ) 

Number of Months = VAR PrevDte = [Previous Date With Different Rate] RETURN IF ( ISBLANK ( PrevDte ), BLANK (), DATEDIFF ( PrevDte, MAX ( Periods[Date] ), MONTH ) ) 

Previous Date With Different Rate = IF ( HASONEVALUE ( Charges[Acct #] ), VAR CurRate = MAX ( Charges[Rate] ) VAR CurPeriod = MAX ( Periods[Date] ) VAR Res = CALCULATE ( MAX ( Charges[Month] ), Charges[Rate] <> CurRate, Periods[Date] < CurPeriod ) RETURN Res, BLANK () ) 

Previous Rate = VAR PrevDte = [Previous Date With Different Rate] RETURN IF ( ISBLANK ( PrevDte ), BLANK (), CALCULATE ( MAX ( Charges[Rate] ), Periods[Date] = PrevDte ) ) 

I hope this will help you find your solution

Craigc3814 · Oct 10, 2018

This is very close to what I did. Instead I created a table of the 12 most recent invoices with a column showing invoice month. I then made a calculated column called months ago where I took the current month and year then subtracted from the invoice month and year. This gives me how many months ago the bill occurred.

From there I went into powerBI and did a lookup on the max value and returned the months ago column.

My issue is now I need to do a lookup in powerBI for the second highest, and for the 3rd highest.

Thanks for always answering my posts!!! Much appreciated.

VBA Geek · Oct 10, 2018

My issue is now I need to do a lookup in powerBI for the second highest, and for the 3rd highest.

Hi Craig, what do you mean by 2nd highest and 3rd highest ? Can you show me an example of these would be?

Craigc3814 · Oct 10, 2018

It's government data in a sense so I can't exactly post it. Basically Max brings back the highest value. From there I look up the months ago field off of the highest value and I know how long ago it was. I then need to say Okay, what's the second highest usage month and how many months ago was that.

I'm doing all of this to do a really weird calculation at the end. We get billed on the highest usage month 50% of the usage for the next 12 months. Once that 12 months falls off it needs to return the next highest value and calculate that out for 12 months at 50%.

Overly confusing I understand. I'm really close, if I can get the second highest value by asset to return the. Then third highest (I think rank is what I need to do).

horseyride · Oct 10, 2018

Powerquery / M
Start with single table called Table1 that has all the data in it [Acct#,Month,Rate,Code,Usage] including most recent month
Code below will generate table picking out the most recent date, the related Usage, and the number of consecutive months with that usage for same Acct#/Rate

qBase

Code:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Base = Table.TransformColumnTypes(Source,{{"Acc#", type text}, {"Month", type date}, {"Rate", type text}, {"Code", type text}, {"Usage", Int64.Type}})
in Base

fnTry

Code:

(Z1 as text, Z2 as text, Z3 as date) =>
let source=  qBase,
#"Filtered Rows" = Table.SelectRows(source, each [#"Acc#"] = Z1 and [Rate] = Z2 and [Month] = Z3),
Z4 = Date.AddMonths(Z3,-1),
#"Filtered Rows2" = Table.SelectRows(source, each [#"Acc#"] = Z1 and [Rate] = Z2 and [Month] = Z4),
Z5= #"Filtered Rows"{0}[Usage],
Z6= if Table.RowCount(#"Filtered Rows2") = 0 then null else #"Filtered Rows2"{0}[Usage],
Compare = if Z5<>Z6 or Z6=null then 0 else fnTry(Z1,Z2,Z4) + 1
in Compare

qMax

Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Base = Table.TransformColumnTypes(Source,{{"Acc#", type text}, {"Month", type date}, {"Rate", type text}, {"Code", type text}, {"Usage", Int64.Type}}),
    #"Grouped Rows" = Table.Group(Base, {"Acc#", "Rate"}, {{"Max", each List.Max([Month]), type date}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows",{"Acc#", "Rate", "Max"},Base,{"Acc#", "Rate", "Month"},"Base",JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Base", {"Usage"}, {"Usage"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table1", "Custom", each fnTry([#"Acc#"], [Rate], [Max])),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom", {{"Custom", 0}}),
    #"Calculated Absolute Value" = Table.TransformColumns(#"Replaced Errors",{{"Custom", each _+1, type number}})
in
    #"Calculated Absolute Value"

Craigc3814 · Oct 10, 2018

PowerBi's Max or Rank function seem like much better options. It will limit the amount of code the query is running and speed the tool up. It will have 3 years worth of invoices in it with about 500 lines per invoice.

VBA Geek · Oct 10, 2018

Craigc3814 said:
It's government data in a sense so I can't exactly post it. Basically Max brings back the highest value. From there I look up the months ago field off of the highest value and I know how long ago it was. I then need to say Okay, what's the second highest usage month and how many months ago was that.

I'm doing all of this to do a really weird calculation at the end. We get billed on the highest usage month 50% of the usage for the next 12 months. Once that 12 months falls off it needs to return the next highest value and calculate that out for 12 months at 50%.

Overly confusing I understand. I'm really close, if I can get the second highest value by asset to return the. Then third highest (I think rank is what I need to do).

So, following my first post, I am assuming I am showing the previous value and how long ago it was. Then you also want to know the previous value prior to the previous value and how long ago this was ?

Counting back until a change occurs

Craigc3814

Board Regular

Excel Facts

VBA Geek

MrExcel MVP

Craigc3814

Board Regular

VBA Geek

MrExcel MVP

Craigc3814

Board Regular

horseyride

Board Regular

Craigc3814

Board Regular

VBA Geek

MrExcel MVP

Similar threads

Forum statistics

Share this page

Counting back until a change occurs

Board Regular

Excel Facts

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

Board Regular

Board Regular

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock