Finding Days Since Last Invoice and Sum of Sales for Each Customer

[Anonymous]

Good afternoon everyone, 

Completely new to Power BI. We are migrating from Tableau to Power BI and I am having some issues re-creating some of our reports. Any help that anyone can provide would be greatly appreciated. 

Overall Goal:

To have the customers ranked from longest time since last invoice to most recent. 

1. Many customers have multiple invoices

2. Need to be able to see sum total of their sales in the same view

3. The is all from one Sheet

Many of the methods I have tried came from this post: https://community.powerbi.com/t5/Desktop/Find-Latest-Transaction-Date-for-Each-Customer/td-p/39037/p...

I have tried the following to get the most recent order date:

Started with a table, dropped in customer name then customer number before adding in the calculated date column (have also tried in reverse by starting table with each of the options below)

Statements Attempted: 

Last Order Date = max('Sheet1'[RO Date Invoiced])  - this provides the most recent invoice date found in the data and applies it to every observation

MaxAge = CALCULATE(MAX('Sheet1'[Age])) - this provides the most recent invoice date, but ungroups the customer so I am getting the invoice date for every observation rather than only the most recent invoice for each customer

Last Transaction = LASTDATE('Sheet1'[RO Date Invoiced]) - same results as MaxAge above

Last Transaction = CALCULATE(LASTDATE('Sheet1'[RO Date Invoiced])) - does not appear to change anything from the last two methods

Days Since = DATEDIFF('Sheet1'[Current Date], CALCULATE(MAX('Sheet1'[RO Date Invoiced])), DAY) - gives me the right amount of time since the invoice date, but still has multiple entries for each customer

 

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

You could create measures similar to below:

Days Since =
DATEDIFF (
    'Sheet1'[Current Date],
    CALCULATE (
        MAX ( 'Sheet1'[RO Date Invoiced] ),
        ALLEXCEPT ( 'Sheet1', 'Sheet1'[CustomerName] )
    ),
    DAY
)

Rank =
RANKX (
    ALLSELECTED ( 'Clothing Sales'[Category] ),
    CALCULATE ( SUM ( 'Clothing Sales'[Sales] ) )
)

Please add [CustomName] and above two measures into table visual. And add [sales] field into table visual, choose "Sum" as its aggregation function, it will sum up sales per customer automatically.

Best regards,

Yuliana Gu

[Anonymous]

This worked great. Thank you so much!!!

[v-yulgu-msft]

Hi @Anonymous,

From current information, it is not clear what your are trying to achieve. Please provide sample data (table structure and data records) of each table, and show us your desired result. If necessary, illustarte how to do calculation with examples.

Regards,

Yuliana Gu

[Anonymous]

This is a small data set example. All the data comes from one table just far more rows. I needed to rename some of the observations, but the format is the same. 

Here is an example of how this report looked in Tableau. I have whited out the customer names, but as you can see, the goal is to identify churn risk customers by showing customers which have not had an invoice in 30-90 days and have them ranked by descending da

[Greg_Deckler]

Sample data is tremendously helpful. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490