Help with $ amount of customer revenue reductions /& additions from one period to another

[charleshale]

I'm trying to create measures that show:

(i) Revenue increase from one period to another from 100% New Customers aka ("New Named Accounts")

(ii) Revenue increases from one period to another from existing customer addons (aka "AddOns")

(iii) Revenue losses from one period to another from existing customer reductions (aka "Reductions")

(iv) Revenue loss from one period to another 100% customer losses ("Cancellations")

 Here's the sample data I am using, below, and here in a pbix (https://1drv.ms/u/s!AkwttVUD7SbrjCa7ZlpZC47UwMJd).

 The ideal results would show these numbers:  

Reductions / addons are the ones I'm struggling with, although through the following 2 measures (the beautiful one is from @OwenAuger ) I am able to get close....

//$Total_PrevPeriod = 
 VAR DateCount =
    COUNTROWS ( 'Date' )
VAR PeriodType =
    SWITCH (
        TRUE (),
               // Complete year selected
        AND (
            HASONEVALUE ( 'Date'[Year] ),
            DateCount = COUNTROWS ( PARALLELPERIOD ( 'Date'[Date], 0, YEAR ) )
        ), "year",     
        // Complete quarter selected
        AND (
            HASONEVALUE ( 'Date'[Quarter] ),
            DateCount = COUNTROWS ( PARALLELPERIOD ( 'Date'[Date], 0, QUARTER ) )
        ), "quarter",   
        // Complete month selected     ///CH- MAY NEED TO MAKE YR-MONTH///
        AND (
            HASONEVALUE ( 'Date'[YYYYMM] ),
            DateCount = COUNTROWS ( PARALLELPERIOD ( 'Date'[Date], 0, MONTH ) )
        ), "month",   
        // YTD period selected (takes precedence over QTD)
        AND (
            HASONEVALUE ( 'Date'[Year] ),
            DateCount = COUNTROWS ( DATESYTD ( 'Date'[Date] ) )
        ), "year",
        
        // QTD period selected
        AND (
            HASONEVALUE ( 'Date'[YYYY-Q] ),
            DateCount = COUNTROWS ( DATESQTD ( 'Date'[Date] ) )
        ), "quarter"
    )
RETURN
    SWITCH (
        PeriodType,
        "year", CALCULATE ( [USDE Revs], PREVIOUSYEAR ( 'Date'[Date] ) ),
        "quarter", CALCULATE ( [USDE Revs], PREVIOUSQUARTER ( 'Date'[Date] ) ),
        "month", CALCULATE ( [USDE Revs], PREVIOUSMONTH ( 'Date'[Date] ) )
    )





//($Reductions) = SUMX(FILTER(SUMMARIZE(
    VALUES(Table2[Customer])  
      , "Revs", [USDE Revs] 
      , "PriorPeriodRevs", [//$Total_PrevPeriod] )
      , [Revs]>0 && [Revs] < [PriorPeriodRevs] ),-([PriorPeriodRevs]-[Revs])
)

...such that I can get the table to display when I include the Customer element, but I am unable to get the totals to work.  Any advice?

[v-yuta-msft]

@charleshale ,

//$Customers_Return = 
CALCULATE (
    [//RevenuemMeasureHere],
    CALCULATETABLE (
        VALUES ( Table1[Customer Name] ),
        VALUES ( Table1[Customer Name]),
        FILTER (
            ALL ( 'Date' ),
            'Date'[Date] < MIN ( 'Date'[Date] )
        )
    )
)

..because new customers are the difference between revenue and revenue from returning customers, but what is good logic for (ii-iv)? 

In excel, it could be an array:  Reductions  = SUM ( IF (  ( CustomerRangePeriod2 < CustomerRangePeriod1 ) * ( CustomerRangePeriod1 >0 )*( CustomerRangePeriod2 > 0 ), ( CustomerRangePeriod1 ) - ( CustomerRangePeriod2 )  -- in english, "if the revenue for each customer in a row from the current period is less than that of the prior period but both are greater than zero, measure this reduction by subtracting the first period row from the second (assuming one wants the reduction as a negative number).  

---

Could you please share some sample data and give the expected result?

Regards,

Jimmy Tao

[charleshale]

Here’s the data and question outlined much more clearly with easy data set and desired results. Thank you, Jimmy Tao

https://community.powerbi.com/t5/Desktop/Sum-of-all-total-revenue-decrease-from-customers-who-reduce...

[charleshale]

Here’s the data and question outlined much more clearly with easy data set and desired results.
https://community.powerbi.com/t5/Desktop/Sum-of-all-total-revenue-decrease-from-customers-who-reduce...
Thank you, Jimmy Tao

[charleshale]

Hi, Jimmy.   2 things:

1.  The on the data, it looks like my dropbox link was broken.  Apologies.   Here's a onedrive link: https://1drv.ms/u/s!AkwttVUD7SbrjCLxQ1M6jf0xeryf

2. I think @Ashish_Mathur is the closest to this.  

//$Redux = SUMX(FILTER(SUMMARIZE(
    VALUES(Table1[Clean Account Name])  
      , "Revs", [USDE Revs] 
      , "PriorPeriodRevs", [//$Revenue_PrevPeriod] )
      , "Revs">0&"Revs"<"PriorPeriodRevs"),"PriorPeriodRevs"-"Revs"
)

I am working the a measure like the above but getting a text / value mismatch.  Getting close though....

[charleshale]

Update aha!  This seems close to working:

//$Redux = SUMX(FILTER(SUMMARIZE(
    VALUES(Table1[Clean Account Name])  
      , "Revs", [USDE Revs] 
      , "PriorPeriodRevs", [//$Customers_Distinct_PrevPeriod] )
      , [Revs]>0 && [Revs] < [PriorPeriodRevs] ),[PriorPeriodRevs]-[Revs]
)

[Cmcmahan]

You say it's close to working, what's wrong with it?  Are you having trouble finding the list of distinct customers from the previous period?

Is the .pbix file linked above the most current iteration of what you're working on? If so, I can take a look.

[charleshale]

Here’s the latest post.
https://community.powerbi.com/t5/Desktop/Sum-of-all-total-revenue-decrease-from-customers-who-reduce...
Not solved yet. It was getting so complicated that I redid the dataset and file and included the correct answers