locked
How to make a measure that caculate this example. RRS feed

  • Question

  • Sum of Xct_Amount    Column Labels   
    Row Labels Product  7/20/2011   7/27/2011 
    Fujitsu      
    23532 Sabre NExT 300 with Anneal                    2,854,325        2,854,325
    24101 Upgrade, Sabre                          62,322              62,322
    24102 Upgrade, Sabre                          62,322              62,322
    24109 Upgrade, Sabre                        236,822            236,822

    Values are in column named sum of xct_amount, the date in column named Freeze_date.

    How to make a measure that can caculate the difference of sum of xct_amount  between this two dates(my date column is always between 7 days).  i know there is caculation in powerpviot that shows the difference from recent date to previous, but if i do so, i wont be able to filter the rows with 0. So i want create a measure that auto caculate the difference from recent Freeze date to the previous. 

    Friday, August 19, 2011 5:28 PM

Answers

  • BigIron,

    There are many ways, the one I would use is this:

    • Defne a calculated column in the date table, which contains the value of the previous date
    • Using CALCULATE, you can compute the sum of amount, in the filter context of the current date, using the previous one

    Then, it is a matter of a subtraction. Clearly, all these formulas will work only if COUNTROWS(VALUES(Date[DateId])) = 1, otherwise they should be blanked.

    If you need the formulas, please share the workbook, just to make my life easier. :)

     


    Alberto Ferrari
    http://www.powerpivotworkshop.com
    • Marked as answer by Challen Fu Saturday, August 27, 2011 9:05 AM
    Saturday, August 20, 2011 8:02 AM