Modified Amounts Across Quarter

[Anonymous]

Hello! 

I have inherited a PowerBI report that shows items that have been modified in Sharepoint and the amount on each date by Project ID. Goal is to see if billing quarter was changed, what was the change amount? 

Below is the formula in DAX to pull in the dollar amount data. Any help to decipher the below would be appreciated! Preferably line by line if possible. Thank you in advance! 

Change QTR test =

Var _Previous = CALCULATE(sum([Quarter Order]),filter(all('Final Cost Log'),'Final Cost Log'[ProjectID] = EARLIER('Final Cost Log'[ProjectID]) && [Rank]=EARLIER('Final Cost Log'[Rank])-1))

Var _Previous1 = CALCULATE(sum([Quarter Order]),filter(all('Final Cost Log'),'Final Cost Log'[ProjectID] = EARLIER('Final Cost Log'[ProjectID]) && [Rank]=EARLIER('Final Cost Log'[Rank])+1))

var cal = [Quarter Order]-_Previous

var cal1 = [Quarter Order]-_Previous1

var change = if(cal > 0, ('Final Cost Log'[Final Cost]), if(cal1 < 0, -'Final Cost Log'[Final Cost]))

var cost = if(not(isblank(change)),'Final Cost Log'[Final Cost])

var test = CALCULATE(sum([Final Cost]),filter(all('Final Cost Log'),'Final Cost Log'[ProjectID] = EARLIER('Final Cost Log'[ProjectID]) && [Rank]=EARLIER('Final Cost Log'[Rank])-1))

return

change

Please let me know if I can (attempt to) clarify anything above! 

[Anonymous · Accepted solution]

Hi @Anonymous

Var _Previous = CALCULATE(sum([Quarter Order]), filter(all('Final Cost Log'), 'Final Cost Log'[ProjectID] = EARLIER('Final Cost Log'[ProjectID]) && [Rank] = EARLIER('Final Cost Log'[Rank]) - 1))

This calculates the sum of [Quarter Order] for the previous rank within the same ProjectID.

Var _Previous1 = CALCULATE(sum([Quarter Order]), filter(all('Final Cost Log'), 'Final Cost Log'[ProjectID] = EARLIER('Final Cost Log'[ProjectID]) && [Rank] = EARLIER('Final Cost Log'[Rank]) + 1))

This calculates the sum of [Quarter Order] for the next rank within the same ProjectID.

var cal = [Quarter Order] - _Previous

This calculates the difference between the current [Quarter Order] and the previous [Quarter Order].

var cal1 = [Quarter Order] - _Previous1

This calculates the difference between the current [Quarter Order] and the next [Quarter Order].

var change = if(cal > 0, ('Final Cost Log'[Final Cost]), if(cal1 < 0, -'Final Cost Log'[Final Cost]))

This determines the change in Final Cost based on the differences calculated in cal and cal1.

if(cal > 0, ('Final Cost Log'[Final Cost]) assigns the Final Cost if cal is positive.
if(cal1 < 0, -'Final Cost Log'[Final Cost]) assigns the negative Final Cost if cal1 is negative.

var cost = if(not(isblank(change)), 'Final Cost Log'[Final Cost])

This assigns the Final Cost if change is not blank.

var test = CALCULATE(sum([Final Cost]), filter(all('Final Cost Log'), 'Final Cost Log'[ProjectID] = EARLIER('Final Cost Log'[ProjectID]) && [Rank] = EARLIER('Final Cost Log'[Rank]) - 1))

This calculates the sum of [Final Cost] for the previous rank within the same ProjectID.

return change

This returns the value of change.

This formula is designed to identify changes in the billing quarter and calculate the corresponding change amount in Final Cost.

Regards,

Nono Chen

If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.