cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rajveer1117
Helper III
Helper III

Running total by month based on selected year

Hi Team,

I need your help in solving the below problem.

Need to find out the running total based on selected year and show by month,here is sample data.

rajveer1117_1-1606336146734.png

Thanks,
Raj

1 ACCEPTED SOLUTION

Hi,

 

Please try the formula.

Running total =

CALCULATE(

    SUMX(

        SUMMARIZE( Sheet1, [Month], "_test", [Test] ),

        [Test]

    ),

    FILTER(

        ALLSELECTED(Sheet1),

        Sheet1[Month] <= MAX([Month])

    )

)

 

v-lionel-msft_0-1606703534435.png

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

8 REPLIES 8
themistoklis
Super User
Super User

@rajveer1117 

 

Try the following formula:

 

Cumulative_Actual = 
CALCULATE (
    SUM ( Sheet1[Values] ),
    FILTER( ALL (Sheet1 ),
    Sheet1[Month] <= MAX( Sheet1[Month] ))
)

 

Just to give you more information, the test field is a calculated measure of average of 4 year.

Hi,

 

Please try the formula.

Running total =

CALCULATE(

    SUMX(

        SUMMARIZE( Sheet1, [Month], "_test", [Test] ),

        [Test]

    ),

    FILTER(

        ALLSELECTED(Sheet1),

        Sheet1[Month] <= MAX([Month])

    )

)

 

v-lionel-msft_0-1606703534435.png

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

@rajveer1117 

 

It is a bit confusing now.

Do you require cumulative total across 12 months on a selected year; based on the initial description?

 

or Average across selected years ... and cumulative sum on this average, across months?

If you could send a sample table of how you want the output table to look like that would be great

 

 

this is what i am seeing in the output after implementing.

rajveer1117_0-1606337173244.png

 

lkalawski
Super User
Super User

Hi @rajveer1117 

Did you try to use this pattern:

https://www.daxpatterns.com/cumulative-total/

 

Please add the sample data or .pbix file without personal information and I will try to help you. 



_______________
If I helped, please accept the solution and give kudos! 😀

Here i want to show cummulative for each year by month.

I tried it but it is not giving the righ result.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.