Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Kolumam
Post Prodigy
Post Prodigy

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

@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

19 REPLIES 19
Ashish_Mathur
Super User
Super User

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/

thanks ,you are Gunies 

Thank you for your kind words.


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

@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]

          )

        )

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

@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.

Anonymous
Not applicable

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

Anonymous
Not applicable

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

Hi,

Share some data and show the expected result.


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

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.

 

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/
Anonymous
Not applicable

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

Hi,

You must create a Calendar Table.


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

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.

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/
Anonymous
Not applicable

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

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/
Anonymous
Not applicable

@Ashish_Mathur 

 

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

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/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.