Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Modified Amounts Across Quarter

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's avatar
    Anonymous
    1 year ago

    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.

     
     
     
     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.