Forum Discussion

chamue329's avatar
chamue329
Icon for Helper II rankHelper II
6 years ago
Solved

Daily change rate with multiple customers

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 meas...
Need Help
Tips and Tricks
  • az38's avatar
    az38
    6 years ago

    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's avatar
chamue329
Icon for Helper II rankHelper II
6 years ago

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

az38's avatar
az38
Icon for Community Champion rankCommunity Champion
6 years ago

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's avatar
    chamue329
    Icon for Helper II rankHelper II
    6 years ago

    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