cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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 IV
Super User IV

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 IV
Super User IV

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 IV
Super User IV

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
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors