Forum Discussion
Calculate the Difference From Previous Month (Previous Month Expenses By ItemID)
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
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
