Day over Day % change

[Anonymous]

Hi I'm trying to calculate % change over day but the formula isn't working. I think it's because of the category column frac name I have to sum the data based on frac name but not sure how to fix this

Measure =

VAR previousDateWithPrice =

CALCULATE (

MAX ( 'Optimization Benefit'[Date] ),

FILTER ( ALL( 'Optimization Benefit'), 'Optimization Benefit'[optimization_benefit] > 0 && 'Optimization Benefit'[Date]< MAX ( 'Optimization Benefit'[Date]) )

)

VAR previousPrice =

CALCULATE (

SUM ( 'Optimization Benefit'[optimization_benefit] ),

FILTER ( ALL ( 'Optimization Benefit' ), 'Optimization Benefit'[Date] = previousDateWithPrice )

)

RETURN

DIVIDE ( SUM ( 'Optimization Benefit'[optimization_benefit] ) - previousPrice, previousPrice )

[V-lianl-msft]

Hi @Anonymous ,

Sorry, based on your description, it is difficult to check your issue.

Please share a sample pbix or post the sample data.

Please remove any sensitive data before uploading.

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Best way for that is to use a date table and time intelligence. Please refer to examples below

This Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
Last Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))
Last Day = CALCULATE(sum('order'[Qty]), previousday('Date'[Date]))

Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9

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 :radacad sqlbi My Video Series Appreciate your Kudos.