Forum Discussion

bw70316's avatar
bw70316
Helper V
7 years ago
Solved

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 addi...
Need Help
  • bw70316's avatar
    bw70316
    7 years ago

    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)
v-yuta-msft's avatar
v-yuta-msft
Community Support
7 years ago

Hi bw70316,

 

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
Table1[Revenues Received] - Previous_Month_Revenues

 

 

Regards,

Jimmy Tao

bw70316's avatar
bw70316
Helper V
7 years ago

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)