Refer to previous rows within a table

[Anonymous]

USER	DATE	BALANCE	RECD
A	01/01/2019	100000	65000
A	02/01/2019	90000
A	03/01/2019	85000
A	07/01/2019	55000
A	08/01/2019	54000
A	10/02/2019	80000	60000
A	11/02/2019	79000
B	01/01/2019	40000
B	03/01/2019	100000	70000
B	09/01/2019	95000
B	01/02/2019	85000
C	01/01/2019	68000
C	07/01/2019	85000	50000
C	09/01/2019	80000
C	21/01/2019	60000
C	22/01/2019	57000
C	01/02/2019	45000
C	06/02/2019	70000	30000
C	10/02/2019	65000

Hi All,

Am a novice Power BI user. I have the data similar to the table above and would like to arrive at a ‘rate of spend’ for each user between receipts

Example:

User A: Between 1/1 and 10/2 i.e. 40 days, user A used up 100000 – (80000 – 60000) = 80000 units at an average spend/day of 2000 units / day

User B: Nothing to be calculated as there is only 1 receipt

User C: Between 7/1 and 6/2 i.e. 30 days, user C used up 85000 – (70000 – 30000) = 45000 units at an average spend/day of 1500 units / day.

Could you please assist with how I go about this?

[MFelix]

Hi @Anonymous ,

Create a calendar table not related with your table the add the following measure:

average spend/day =
VAR Maximum_date =
    MAX ( CalendarTable[Date] )
VAR minimum_date =
    MIN ( CalendarTable[Date] )
RETURN
    (
        CALCULATE ( SUM ( Balance_table[BALANCE] ); Balance_table[DATE] = minimum_date )
            - (
                CALCULATE ( SUM ( Balance_table[BALANCE] ); Balance_table[DATE] = Maximum_date )
                    - CALCULATE ( SUM ( Balance_table[RECD] ); Balance_table[DATE] = Maximum_date )
            )
    )
        / DATEDIFF ( minimum_date; Maximum_date; DAY )

Check attach the PBIX file.

Regards,

MFelix

[Anonymous]

Hi @MFelix ,

Am unable to open the pbix file as you are using a later version of Power BI. (mine is an organization account so unable to update on my own). 

Would the measure still work even if there were multiple receipts - asking as when I see a min / max date am thinking this might just work for 1 set of receipts?

[MFelix]

Hi @Anonymous ,

The measure was created based on your post and the calculations you present.

When you refer having more receipts is within the interval you are refering?  

If it only refering to the receipts part you can change the measure to:

average spend/day =
VAR Maximum_date =
    MAX ( CalendarTable[Date] )
VAR minimum_date =
    MIN ( CalendarTable[Date] )
RETURN
    (
        CALCULATE ( SUM ( Balance_table[BALANCE] ); Balance_table[DATE] = minimum_date )
            - (
                CALCULATE ( SUM ( Balance_table[BALANCE] ); Balance_table[DATE] = Maximum_date )
                    - CALCULATE ( SUM ( Balance_table[RECD] ); Balance_table[DATE] <= Maximum_date && Balance_table[DATE] >= Mininum_date)
            )
    )
        / DATEDIFF ( minimum_date; Maximum_date; DAY )

Can you share some more sample calculations to what you refer.

Regards,

MFelix

[Anonymous]

Hi Mfelix,

How do I attach the excel extracted raw data to this post?

[MFelix]

You need to add a one drive, Dropbox, wetransfer or similar link to the post.

Regards,
MFelix

[Anonymous]

Hi MFelix,

Have uploaded the file here.

https://docs.google.com/file/d/0B5BGbSNSD0saNjd0TUI1eVhaWFRwRzQ1SjYycE1mOWdYZlkw/edit?usp=docslist_a... 

Really appreciate your help with this.

[MFelix]

Hi @Anonymous ,

It's asking me for a password can you share it without password for access?

Regards,

MFelix

[Anonymous]

Hi @MFelix ,

Could you try now please.

https://docs.google.com/file/d/1rh9sTxtAbdoRF5LlVYhZw8jlmbpHxBYb/edit?usp=docslist_api&filetype=msex...

[Ashish_Mathur]

Hi,

Do you want a calculated column formula?

[Anonymous]

Hi @Ashish_Mathur ,

yes, that is what I'm looking for. 

Adding the link for an extract from the raw data (including what the calculations would look like).

https://docs.google.com/file/d/1rh9sTxtAbdoRF5LlVYhZw8jlmbpHxBYb/edit?usp=docslist_api&filetype=msex... 

[Ashish_Mathur]

Hi,

These calculated column formulas work

Spend btw Recpt = if(AND([Recd]>0,LOOKUPVALUE([Balance],Data[eventTime],CALCULATE(MAX(Data[eventTime]),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[eventTime]<EARLIER(Data[eventTime])&&Data[Recd]>0)),Data[ID],Data[ID])<>BLANK()),LOOKUPVALUE([Balance],Data[eventTime],CALCULATE(MAX(Data[eventTime]),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[eventTime]<EARLIER(Data[eventTime])&&Data[Recd]>0)),Data[ID],Data[ID])-([Balance]-[Recd]),BLANK())

Avg spend /day = =[Spend btw Recpt]/([eventTime]-CALCULATE(MAX(Data[eventTime]),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[eventTime]<EARLIER(Data[eventTime])&&Data[Recd]>0)))

Hope this helps.

[Anonymous]

Hi Ashish,

Works like a charm!!! Thanks a ton

Found an infrequent error in the data that I'd like to find a workaround for.

If the Recd is 0 but the balance increases (for the same ID), then can I have the Recd = Diff of consecutive balances?

[Anonymous]

To be more precise:

If the Recd is 0 but the balance increases (for the same ID), then can I have the Recd = Diff of consecutive balances - average spend for the number of days between the consecutive balances

[Ashish_Mathur]

Hi,

I do not understand.  Share some data and show the expected result.

[Anonymous]

Hi @Ashish_Mathur 

I've edited rows 165 & 166 of the earlier data as an example. 

The balance increases from Row 164 to Row 165 but the reporting of the Receipt has been missed out / reported on the incorrect date.

In excel, I've handled this by introducing a Recd_mod column that corrects for this. All the formulae for Spend / day, Spend btw receipt and Avg. spend / day now reference the Recd_mod column.

Here is the link to the raw data

https://docs.google.com/file/d/13qUVAlj2cmzxmNNLAojdFOvKJ3N6iQoT/edit?usp=docslist_api&filetype=msex...

[Ashish_Mathur]

Hi,

You have completely changed the logic now.  This may take time to solve.  If and when i have the time, i will try to solve the problem.

[Anonymous]

Thanks @Ashish_Mathur 

Was forced into making the change due to the data quality.

Thank you for looking into this.

[Ashish_Mathur]

Hi,

Given the first 4 columns as inputs, try these calculated column formulas

Spend btw recpt = if(AND([Recd _mod]>0,LOOKUPVALUE([Balance],Data[eventTime],CALCULATE(MAX(Data[eventTime]),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[eventTime]<EARLIER(Data[eventTime])&&Data[Recd _mod]>0)),Data[ID],Data[ID])<>BLANK()),LOOKUPVALUE([Balance],Data[eventTime],CALCULATE(MAX(Data[eventTime]),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[eventTime]<EARLIER(Data[eventTime])&&Data[Recd _mod]>0)),Data[ID],Data[ID])-([Balance]-[Recd _mod]),BLANK())

Avg Spend/ day = [Spend btw Recpt]/([eventTime]-CALCULATE(MAX(Data[eventTime]),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[eventTime]<EARLIER(Data[eventTime])&&Data[Recd _mod]>0)))

Hope this helps.

[MFelix]

Hi  @Anonymous 

Using your datasource and the same measure I have send on the first post it's working as expected.

Can you please check it?

Regards,

MFelix