Daily change rate for various aggregation level (ie. customer by state or national)

[chamue329]

Hi,

I have a table with running sales by state by customer by date iterating down.  I received some help with DAX measures to calculate the daily change and % change, however, the results are negative values when I try to aggregate up to state or even national.  Here's my table sample:

Here is the fantastic DAX syntax for daily change and daily change % that @az38 constructed :

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)))

% 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)

As you can see in the table example, when I remove the customer and display results by state, I get negative values.  Anyone with ideas? I've spent the past day trying to figure this out.  Thanks.

[chamue329 · Accepted solution]

@parry2k yes those are the desired results. It just needs to scale to the agg level (State or National) in my visual.

[amitchandak]

Try Measure with a date calendar

Current  = SUM(Sales[Running Sales]) 
//Or
Current  = SUM(Sales[Running Sales], filter('Date','Date'[Date]=max('Date'[Date]))

Day behind Sales = CALCULATE(SUM(Sales[Running Amount]),dateadd('Date'[Date],-1,Day))
//OR
Day behind Sales = SUM(Sales[Running Sales], filter('Date','Date'[Date]=max('Date'[Date])-1))


diff  =[Current] =Day behind Sales

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/