Calculate the Difference From Previous Month (Previous Month Expenses By ItemID)

[aboshabo]

I'm using Microsoft Access 2016 to developing a new program, each month the data entry enter in a row the date, the total budget planed and cumulative expending amount. I am trying show the monthly bases experiences filtered/grouped by ItemID. For example, I have an ItemID called 2222,this ItemID has three entry in different dates, Sep 2018, Jan 2019 and August 2019, I need a measure "Previous Month Expenses" shows max date of MonthlyExpenses - previous of MonthlyExpenses group by/filter by ItemID (for instance in August 2019, the cumulative value was 170000, the cumulative value for Jan 2019 was 125000, the cumulative value for Sep 2018 was 25000, so the difference is 45000 for this item).

Here is the URL of startup:

https://www.dropbox.com/s/5xo9t57ueyc6299/Previous%20Month%20Expenses.pbix?dl=0

Thanks for your support!

Best Regards

Mahmoud

[Anonymous]

Measure = CALCULATE(Table[Variable],DATEADD(Variable,-1,MONTH)) 

it is something like that, follow the instructions from PBI formula. That should do the job. Else, just right click and go to quick measures and then time intelligence measures.

[parry2k]

@aboshabo there are so many columns in your table and example you gave doesn't resonate with what is in your pbix. Can you share example based on your data and also how would you like to see the end result. Do you want a table visual with items and difference from previous month

[aboshabo]

@parry2k  thanks for your quick response! I remove the extra columns and updated the example, yes, I need a table visual with items and difference from latest month entry for same item.

Best Regards

Mahmoud

[parry2k]

@aboshabo don't see the changes but regardless on which column you want to perform this calculation budget, expense?? 

[aboshabo]

@parry2kthanks for your follow, I hope I replied to your question. I am looking for a result based on "MonthlyExpenses" column. I added an empty measuer called " Previous Month Expenses" for this purpose.

Best Regards

Mahmoud

[parry2k · Accepted solution]

@aboshabo this is how I will do it so that you can see it at anytime, add following measures

Basic Measure = SUM( Test[MonthlyExpenses] )

Cummulative Up to Date = 
CALCULATE( [Basic Measure],  
ALLEXCEPT( Test, Test[ItemID], Test[Date] ), 
FILTER(ALL(Test[Date]), Test[Date] <= MAX( Test[Date] ) ) )

Cummulative Prev Month = 
[Cummulative Up to Date] - [Basic Measure]

here is the output, cummulative difference  is pretty much basic measure