# How to make a measure that caculate this example.

• ### 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

• 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 Saturday, August 27, 2011 9:05 AM
Saturday, August 20, 2011 8:02 AM