cancel
Showing results for
Did you mean:
Kolumam 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? 1 ACCEPTED SOLUTION

Accepted Solutions
anandav Established Member

## Re: Calculating running total based on month

Try using the below measure:

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

17 REPLIES 17
anandav Established Member

## Re: Calculating running total based on month

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

## 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

## Re: Calculating running total based on month

Try using the below measure:

Measure =
CALCULATE(
SUM(Example[Value]),
FILTER(ALL(Example), Example[Num] <= MIN(Example[Num]))
) Hope this helps. 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
kamal_11 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  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
kamal_11 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 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")) 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

## 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
kamal_11 Regular Visitor

## Re: Calculating running total based on month

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

Announcements #### 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?

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

We are super excited for our update this month, as we are releasing two of our top community requests! #### Power Platform Online Conference 