Forum Discussion

PrachiD's avatar
PrachiD
Helper I
8 years ago
Solved

Moving Cumulative Sum

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

General Comment
Interesting Links
Tips and Tricks
Tutorial Requests

9 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    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] )
    )
)
      • PrachiD's avatar
        PrachiD
        Helper I
        8 years ago

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