cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ljone153
Frequent Visitor

12 Month Moving Sum

Hello, I have looked at multiple 12 month moving sum posts, but still havent been able to solve my problem. My Problem is that whenever I put in a 12 month moving sum formula, I get just the same number for all dates as seen below (far right). The following picture also shows one of my 12 month moving sums that I tried.

 

Capture1.PNG

 

Here is the other formula I tried.

 

Capture.PNG

 

Here is the table that I am trying to do the 12 month moving sum on (it is a calculated table):

 

Capture2.PNG

 

And here is my dim Date table for good measure (only use the MonthYear Column).

 

Capture3.PNG

 

If anyone can help me make sense of where I am going wrong, that would be a great help. If you need anymore information, feel free to ask. Thank you!

1 ACCEPTED SOLUTION

Solved the problem by using the new quick measure tool!

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

 

Share the link from where i can download your PBI file.  Show the expected result there.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hello,

 

Here is a link to the PBIX file:

https://drive.google.com/open?id=1iTFMfOQJny3HCAtMrHj0NULx1C3xWOYM

 

Here is a link to the expected results I want in an excel file:

https://drive.google.com/open?id=14SYE23OzvqT7m2nOoVmnW1Jva449DmjU

 

Also, I would like to be able to use a dummy filter on the 12 month sum(specifically "new business" or not) and be able to add it in another measure. I will use it in this formula:

 

PolicyCount/(PreviousYearPolicyCount + Previous 12 month Sum of New Business Policy Count)

 

* I am only having trouble getting the 12 month rolling sum

 

I also figured out why I was only getting repeated numbers in my table visual, it was because I did not connect my PolicyCount table to my other tables.

 

If you need anything else, just let me know! Thank you for the help!

Hi,

 

I cannot download the files from that link.  I get a message saying that i need permission.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Try these link:

https://drive.google.com/file/d/1iTFMfOQJny3HCAtMrHj0NULx1C3xWOYM/view?usp=sharing

 

https://drive.google.com/file/d/14SYE23OzvqT7m2nOoVmnW1Jva449DmjU/view?usp=sharing

 

See if that works, this is the first time doing it. Let me know if there is anything else you need. Thank you!

Hi,

 

That PBI file is 640 MB.  Get it down to only a couple of MB's please and then share the download link.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hello,

 

Unfortunetly, that is impossible without lossing much of the functionality of PowerBI. I could get it down to 7 KB, but the trade off is only being able to use the report view and not having the data and connections view, not being able to edit the query, enter data etc. The PBIX file is at its smallest size with all its functionality. Below is a screenshot of a PBIX that I got the data from the PowerBI Dataset source which is 7KB. If you think you can accomplish it with just the report view then I can send you just that or if you can think of another method. After removing all the unneccsary columns, that only brought the size down by 5 MB, so it looks like the 640 MB file will be necessary. If you need anything else, feel free to ask. Thanks!

 

Capture.PNG

Solved the problem by using the new quick measure tool!

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!