cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper V

Calculated Table for Month over Month Changes

I have a table of this:

July: \$1,959,814

August: \$3,925,980

September: \$5,855,582

October: \$7,910,275

November: \$9,155,231

TOTAL:\$28,806,885

It is taking the previous month total and adding on the current month's expenses. How do I get a table that does the following:

July: \$1,959,814
August= August Total-July= NUMBER I WANT
Sept Total = (August +July) - Sept = NUMBER I WANT
ETC.

The total at the end should be 9,155,231 and each month should be 1.8 to 2.1M based on the subtraction of each month.

Basically, how do I get the difference from month to to month without having to create a SUM('Expenses'[Expensed]),'Expenses'[Month] = "July" and subtracting the difference for each month??

1 ACCEPTED SOLUTION
Helper V

This is likely a workable solution, unfortunately I didn't realize you needed my complete data set as these monthly expenditures are the result of a combination of several expenditure items. When I tried this equation, the column that matched was included several times for the different items.

I was able to achieve my desired result with the following DAX:

TotalRev2 =
VAR CumRev =
CALCULATE([Sum Rev Rec.], FILTER(ALL(Revenues), Revenues[MonthNUM]=MAX(Revenues[MonthNUM])-1))
VAR CurRev= [Sum Rev Rec.]

RETURN

(CurRev-CumRev)
4 REPLIES 4
Super User

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper V

Thanks for responding! I would simply like the difference or the month's total sales by month, and not a cummulative figure. For example, I am currently getting the following output:

 Revenues Received MONTH \$9,061,289.66 11 \$8,924,656.07 10 \$7,099,541.62 9 \$5,301,671.96 8 \$3,804,197.98 7

What I would Like is:

 Revenues Received MONTH \$136,633 11 \$1,825,114.45 10 \$1,797,870 9 \$1,497,474 8 \$3,804,197 7

The ACTUAL Sales by Month and not a cummlative total

Community Support

Hi

To achieve your requirement, you can create a calculate column using DAX formula below:

```Column =
VAR Previous_Month_Revenues = CALCULATE(MAX(Table1[Revenues Received]), FILTER(Table1, Table1[MONTH] = EARLIEST(Table1[MONTH]) - 1))
RETURN
```

Regards,

Jimmy Tao

Helper V

This is likely a workable solution, unfortunately I didn't realize you needed my complete data set as these monthly expenditures are the result of a combination of several expenditure items. When I tried this equation, the column that matched was included several times for the different items.

I was able to achieve my desired result with the following DAX:

TotalRev2 =
VAR CumRev =
CALCULATE([Sum Rev Rec.], FILTER(ALL(Revenues), Revenues[MonthNUM]=MAX(Revenues[MonthNUM])-1))
VAR CurRev= [Sum Rev Rec.]

RETURN

(CurRev-CumRev)

Announcements

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors