Power Pivot ....MEasure to take Month over Month Difference

[daveyc18]

so my source data has two dates in column A....this month and previous month

there's another column "expenses"

im trying to make a measure/dax formula that takes the the difference between this months expenses vs last month for each row of data in the data set and obviously put that in the pivot

 

[Muhammad_Usman]

You can create a DAX measure to calculate the difference between this month's expenses and last month's expenses for each row of data in your dataset and then use that measure in your PivotTable. You can use the DAX `EARLIER` function to reference the current row's values and calculate the difference.

Here's a sample DAX measure for this purpose:

```DAX
MonthlyExpenseDifference =
SUMX(
FILTER(
'YourTableName', -- Replace with the name of your table
'YourTableName'[Date] = MAX('YourTableName'[Date]) -- Filter the current month's data
),
'YourTableName'[Expenses]
)
-
SUMX(
FILTER(
'YourTableName', -- Replace with the name of your table
'YourTableName'[Date] = MAX('YourTableName'[Date]) - 1 -- Filter the previous month's data
),
'YourTableName'[Expenses]
)
```

Here's how this DAX measure works:

1. `SUMX` is used to iterate through the table.
2. The `FILTER` function is used to filter the table to include only the rows for the current month and the previous month.
3. The first `SUMX` calculates the sum of expenses for the current month.
4. The second `SUMX` calculates the sum of expenses for the previous month.
5. The difference between the two sums gives you the monthly expense difference for each row.

Make sure to replace `'YourTableName'` with the actual name of your table and `'YourTableName'[Date]` and `'YourTableName'[Expenses]` with the actual column names for dates and expenses in your table.

After you create this DAX measure, you can add it to your PivotTable as a value to see the monthly expense differences for each row of data.