cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Re: Calculating running total based on month

Hi,

You must create a Calendar Table.


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

Re: Calculating running total based on month

Hi @Ashish_Mathur ,

 

I have Rep_Date column in my data, I can extract or split month and year from Rep_date. Is that good or else I need to create a custom table in Query Editor and mark as table and I need to give relationship to Rep_date. and Can you also help me out with calculated column and make it as the table in power query editor.

Super User
Super User

Re: Calculating running total based on month

Hi,

You may go to Data > Modelling > New Table and write the following formula

Calendar = CALENDAR(MIN(Data[Date]),MAX(Data[Date]))

Create a relationship from the Date column of the Data Table to the Date column of the Calendar Table.  In the Calendar Table, extract the Year and Month by using the following formulas

Year = Year(Calendar[Date])

Month = FORMAT(Calendar[Date],"mmmm")

In your visual, drag Year and Month from the Calendar Table.

Hope this helps.


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

Re: Calculating running total based on month

By using the below formula, the formula is doing sum of 3 measures but not doing the sum for 3 consecutive months. Please help me out to find the sum for 3 measures for 3 consecutive months which I explained in my previous post.

 

 

Capture.JPG

Super User
Super User

Re: Calculating running total based on month

Hi,

I do not see your formula there.  Also, have you followed all steps for creating the Calendar Table and building the relationship?


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

Re: Calculating running total based on month

@Ashish_Mathur 

 

Yes, I have followed the steps which you mentioned in previous post 

Super User
Super User

Re: Calculating running total based on month

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/
LukasBE Regular Visitor
Regular Visitor

Re: Calculating running total based on month

Hi anandav,

 

I was searching for this solution and it works. But in the total row I have the first/smallest value and I would like to have there total amount up to the actual month (in this case to the month 2). See the attached picture.

Any idea how to get it there?

 

Thank you

Lukas

 

running total.PNG

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 240 members 2,453 guests
Please welcome our newest community members: