Calculation of the current month value from the running total column (reset every year)

Question

I have a table like this:

AsIs

I want to calculate the current value of a single month in another column as the difference between this month and previous (grouping by "group"), except January because the value resets every year.

I want to obtain a table like this:

ToBe

I tried different functions as:

MonthlyValue =
CALCULATE(Table[RunningValue], PREVIOUSMONTH(Table[Period]

and

MonthlyValue = 
VAR Cumulative=Table[RunningValue]
VAR PreviousCumulative=
    CALCULATE(
        [RunningValue], 
        DATEADD(Table[Period], -1, MONTH)
    )
RETURN
Cumulative-PreviousCumulative

However, I always obtain the same RunningValue (correct only for jan). Not the difference between actual and previous RunningValue.

Nick A · Accepted Answer · 2024-09-03 13:21:06Z

0

Try this measure:

MonthlyValue = 
VAR thisPeriod = MAXX('Table', [Period]) //current period
VAR thisPeriodMonth = MONTH(thisPeriod) //current month
VAR thisPeriodPrev = EOMONTH(thisPeriod,-1) //last day of previous month 

VAR thisGroup = MAXX('Table', [Group])
VAR thisValue = MAXX('Table', [RunningValue])

VAR prevValue = 
 CALCULATE(MAXX('Table', [RunningValue])
    ,FILTER(ALL('Table')
    ,'Table'[Group] = thisGroup 
    && EOMONTH('Table'[Period],0) = thisPeriodPrev
    )
 )

RETURN IF(thisPeriodMonth <> 1, thisValue - prevValue, thisValue)

Output:

edited Sep 3, 2024 at 13:21

answered Aug 30, 2024 at 14:15

Nick A

1,1202 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

alo Over a year ago

does not work, I get the cumulative monthly sum of something, not the difference of the cumulative sum of this month compared to the previous month

Nick A Over a year ago

Can you please provide the output? I used your exact data to build it and received your expected output.

Collectives™ on Stack Overflow

Calculation of the current month value from the running total column (reset every year)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related