Daily change rate with multiple customers

[chamue329]

Hi,

I have a table with customer sales by date and I need to calculate the daily change and % change (example image below). Is it better to add these two columns in Power Query or create DAX measures?  I've been scouring the message boards but can't seem to find a solution for this scenario. 

Customer	Date	Running Sales	Daily Chg	% Daily Chg
A	3/2/2020	10	-	-
A	3/3/2020	25	15	150%
A	3/4/2020	40	15	60%
A	3/5/2020	97	57	143%
B	3/2/2020	100	-	-
B	3/3/2020	120	20	20%
B	3/4/2020	190	70	58%
B	3/5/2020	220	30	16%

[Greg_Deckler]

This should be basically the same as MTBF - See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

In your case:

Daily Chg =
  VAR __Current = [Running Sales]
  VAR __PreviousDate = 
    MAXX(
      FILTER(
        'Table',
        [Customer] = EARLIER([Customer]) &&
        [Date] < EARLIER([Date])
      ),
      [Date]
    )
  VAR __Previous = 
     MAXX(FILTER('Table',[Customer = EARLIER([Customer]) && [Date] = __PreviousDate),[RunningSales])
RETURN
  __Current - __Previous

[az38 · Accepted solution]

@chamue329 

DAX is appropriate solution foryour task.

try create a measures

Daily Chg = 
var _prevDate = CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Customer]=SELECTEDVALUE('Table'[Customer]) && 'Table'[Date]<SELECTEDVALUE('Table'[Date])))
RETURN
IF(ISBLANK(_prevDate), BLANK(),
SELECTEDVALUE('Table'[Running Sales])-CALCULATE(MAX('Table'[Running Sales]),FILTER(ALL('Table'),'Table'[Customer]=SELECTEDVALUE('Table'[Customer]) && 'Table'[Date]=_prevDate)))

and

% Daily Chg = 
var _prevDate = CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Customer]=SELECTEDVALUE('Table'[Customer]) && 'Table'[Date]<SELECTEDVALUE('Table'[Date])))
RETURN
IF(ISBLANK(_prevDate), BLANK(),
DIVIDE(SELECTEDVALUE('Table'[Running Sales]), CALCULATE(MAX('Table'[Running Sales]),FILTER(ALL('Table'),'Table'[Customer]=SELECTEDVALUE('Table'[Customer]) && 'Table'[Date]=_prevDate))) - 1)

[chamue329]

@az38 This works perfectly.  I was able to trend this out on a line chart and both measures display.  I have a follow-up question, in my table structure there is also a State column. When I add a second filter to my line chart to view it by State, it won't render. Is this simply some chart settings in Power BI to render the visual properly or am I stuck having to create a second set of measures if I wanted to generate for the aggregate of customers by State?  Here's my table field with State included:

State	Customer	Date	Running Sales	Daily Chg	% Daily Chg
CA	A	3/2/2020	10	-	-
CA	A	3/3/2020	25	15	150%
CA	A	3/4/2020	40	15	60%
CA	A	3/5/2020	97	57	143%
CA	B	3/2/2020	100	-	-
CA	B	3/3/2020	120	20	20%
CA	B	3/4/2020	190	70	58%
CA	B	3/5/2020	220	30	16%

[chamue329]

Perhaps a change in the DAX syntax to dynamically adjust to the filter selection (State or Customer) if that is feasible?

[az38]

@chamue329 

in my solution it would be like 

Daily Chg = 
var _prevDate = CALCULATE(MAX('Table'[Date]), FILTER(ALLEXCEPT('Table', 'Table'[Customer],'Table'[State]), 'Table'[Date] < SELECTEDVALUE('Table'[Date])))
RETURN
IF(ISBLANK(_prevDate), BLANK(),
SELECTEDVALUE('Table'[Running Sales]) - CALCULATE(MAX('Table'[Running Sales]), FILTER(ALLEXCEPT('Table', 'Table'[Customer], 'Table'[State]),  'Table'[Date]=_prevDate)))

[chamue329]

@az38  Thanks for the modification - however I'm getting all negative numbers when I filter to States.  It works fine when I have the Customer column included, but once I remove and only look at my table by State, it gets messed up.  One clarification - I don't have rows in my tables that aggregate the State records.  I placed them in my example table for illustration.  I don't know if this will make a difference in your syntax.

Running Sales with daily change