cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Kolumam Member
Member

Calculating running total based on month

Hi All, 

 

How do I calculate running total of yield based on Month. If it's not poosible I also have Month number. Can you please help?

daxx.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
anandav Established Member
Established Member

Re: Calculating running total based on month

@Kolumam,

 

Try using the below measure:

Measure =
CALCULATE(
    SUM(Example[Value]),
    FILTER(ALL(Example), Example[Num] <= MIN(Example[Num]))
)

 

CummulativeTotal1.jpg

Hope this helps.

If this solves your problem please mark as solution.

View solution in original post

17 REPLIES 17
anandav Established Member
Established Member

Re: Calculating running total based on month

@Kolumam,

 

You want a running total (cummulative total).

Check the link below how to do it,

https://whatthetechisthat.wordpress.com/2017/05/30/power-bi-cumulative-totals/

 

If your date field in below table is a date heirarchy, then you can choose only month (and year if you need). You need to have a calendar table and establish relatship with your data table.

 

 Running Toal Measure =

      CALCULATE(

          Sum(Table[Yield],

          FILTER(All(Dim_Date[Date]), Dim_Date[Date] <= MAX(Dim_Date[Date]

          )

        )

Kolumam Member
Member

Re: Calculating running total based on month

I don't have a date field. All I have is a month field which is a text field and a month number.

anandav Established Member
Established Member

Re: Calculating running total based on month

@Kolumam,

 

Try using the below measure:

Measure =
CALCULATE(
    SUM(Example[Value]),
    FILTER(ALL(Example), Example[Num] <= MIN(Example[Num]))
)

 

CummulativeTotal1.jpg

Hope this helps.

If this solves your problem please mark as solution.

View solution in original post

Super User
Super User

Re: Calculating running total based on month

Hi,

 

With a little bit of effort, this should be simple to solve.  Ensure you have a Calendar Table.  Create a relatioship from the Date column of your base table to the Date column of your Calendar Table.  In the Calendar Table, enter this formula in a column to extract the Month name

 

=FORMAT(Calendar[Date])

 

Now drag the Month from the Calendar Table to your visual.  Enter this formula to calculate running yield

 

=CALCULATE([Yield],DATESYTD(Calendar[Date],"31/12"))

 

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

I do have the same issue. I have created the measure , The data is in calculated measure but not in table. I have researched a lot but I'm not getting the solution. I have to do sum for each 3 consecutive month for all 3 measures in below picture

 

 

 

 


Capture.PNG

Super User
Super User

Re: Calculating running total based on month

Hi,

Share some data and show the expected result.


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

Here is the sample data for creating a measure. From this data, I have created a measure for each Rollup location and NPSCAT

1.PNG

 

I have used below formula for creating measure.

promoter Measure = COUNTROWS(FILTER('all data','all data'[Rollup]="EMEALA" && 'all data'[NPSCat] = "Promoter"))
Decorate Measure = COUNTROWS(FILTER('all data','all data'[Rollup]="EMEALA" && 'all data'[NPSCat] = "Detractor"))
 Passive Measure = COUNTROWS(FILTER('all data','all data'[Rollup]="EMEALA" && 'all data'[NPSCat]="Passive"))
 
Capture.JPG
 
from the above measures, I need to calculate the sum of three measures for every 3 consecutive months.
 
Here is the measure I'm using to calculate for every 3 consecutive months 
_Measure = IF(MIN('all data'[Month])>=5,CALCULATE([promoter Measure]+[Passive Measure]+[Decorate Measure],FILTER(ALL('all data'),'all data'[Rep_Date]<=MAX('all data'[Rep_Date])&& MONTH('all data'[Rep_Date])>=MONTH(MAX('all data'[Rep_Date]))-3),CALCULATE([promoter Measure]+[Passive Measure]+[Decorate Measure],FILTER(ALL('all data'),'all data'[Rep_Date]<=MAX('all data'[Rep_Date])))))
 
Please let me know the solution.

 

Super User
Super User

Re: Calculating running total based on month

Hi,

You must have a Calendar Table with a relationship from the Date column of the Data Table to the Date column of the Calendar Table.  In the Calendar Table, extract Year and Month in 2 seperate columns.  To your visual, drag Year and Month from the Calendar Table.  Write this measure

=CALCULATE([promoter Measure]+[Passive Measure]+[Decorate Measure],DATESBETWEEN(Calendar[Date],EDATE(MIN(Calendar[Date]),-2),MAX(Calendar[Date])))

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

Hi @Ashish_Mathur 

 

I don't have the calendar Table in my data, I'm using Rep_Date and I split the month and column from this Rep_Date. Can you please help me out, How can I write the measure If I don't have the Calendar table in my database

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

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: 256 members 2,828 guests
Please welcome our newest community members: