Forum Discussion

aboshabo's avatar
aboshabo
Frequent Visitor
7 years ago
Solved

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 b...
Need Help
Tutorial Requests
  • parry2k's avatar
    parry2k
    7 years ago

    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 

     

aboshabo's avatar
aboshabo
Frequent Visitor
7 years ago

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's avatar
parry2k
Icon for Super User rankSuper User
7 years ago

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