Moving Cumulative Sum

[PrachiD]

Hello,
Need help for Calculating Cumulative Column as shown in above table.
I want to calculate the Moving sum depending upon the fiscal year as well as month.
Suppose if there is Apr-16 then I want the count as it is( ie, 5 in Cumulative Count.)
if it is May then I should get the count as (Apr-16+May-16) ( ie, (5+10)=15 in Cumulative Count.) and so on till Jan-17.
And if there is  Apr-17 then I want the count as it is( ie, 65 in Cumulative Count.) for than I dont need sum for all the previous month it should show me as it is.
if it is May then I should get the count as (Apr-17+May-17) ( ie, (65+70)=135 in Cumulative Count.) and so on till Jan-18.

[Zubair_Muhammad]

HI @PrachiD

Try this solution

First a Calculated Column for Month Numbers

Month Number =
IF (
    MONTH ( TableName[Year-Month] ) > 3,
    MONTH ( TableName[Year-Month] ) - 3,
    MONTH ( TableName[Year-Month] ) + 9
)

Now a column for Years

YEAR =
IF (
    MONTH ( TableName[Year-Month] ) <= 3,
    YEAR ( TableName[Year-Month] ) - 1,
    YEAR ( TableName[Year-Month] )
)

Now you can get Cumulative as follows

Cumulative =
CALCULATE (
    SUM ( TableName[Count] ),
    FILTER (
        ALL ( TableName ),
        TableName[YEAR] = EARLIER ( TableName[YEAR] )
            && TableName[Month Number] <= EARLIER ( TableName[Month Number] )
    )
)

[Zubair_Muhammad · Accepted solution]

@PrachiD

[PrachiD]

Thank you so much.
Can u please explain the Month Number Query why you have taken > 3 ,+9 like that

[Zubair_Muhammad]

Hi @PrachiD

Becasue your Month starts from April which is the 4th month by default

To make April 1 from 4 we have to subtract 3

To make Jan 10 from 1 we have to add 9

[Zubair_Muhammad]

@PrachiD

Alternatively you could use this to get the Month numbers

Month_Number =
SWITCH (
    FORMAT ( TableName[Year-Month], "MMMM" ),
    "January", 10,
    "February", 11,
    "March", 12,
    "April", 1,
    "May", 2,
    "June", 3,
    "July", 4,
    "August", 5,
    "September", 6,
    "October", 7,
    "November", 8,
    "December", 9
)

[Zubair_Muhammad]

@PrachiD

or alternatively this

Month_Number =
SWITCH (
    MONTH ( TableName[Year-Month] ),
    1, 10,
    2, 11,
    3, 12,
    4, 1,
    5, 2,
    6, 3,
    7, 4,
    8, 5,
    9, 6,
    10, 7,
    11, 8,
    12, 9
)

[PrachiD]

[PrachiD]

As per Your Solution I got the answer but on the real time senario the I m facing many problems.
I m having 3 tables with me HeadCount table , Attrition table and Date table.
HeadCount and Attrition table are connected to Date table by month-year.
HeadCount and Attrition table are not connected to each other.

Now for Headcount table and Attrition Table I want to calculate the Cumulative Sum.
That Cumulative logic is as per you told in the above reply.

I want to calculate the % which include sum of cumulative from attrition table and sum of cumulative from HeadCount table.

formula we are writing in Date table Because we does not have relationship between HeadCount and Attrition table and formula is:-

%=(Cumlative(Released)/Cumlative(HeadCount))*100

As per your logic we have derived Calculated Column but it does not work when we are taking percentage

[Zubair_Muhammad]

 @PrachiD

Use this formula in the Date Table

% =
RELATED ( AttritionTable[Cumulative] ) / RELATED ( HeadCountTable[Cumulative] )