cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ISGirl
New Member

Calculate Monthly based on Cumulative Total

Hello, 

I am having issue with Power BI report. The datatsource is SQL server where data is loaded as cumulative total. But My goal is to alculate monthly based on cumulative total. 

Please see data sample below (Posted date is a calculated column in PBI and it's ralted to Calendar table). 

FiscalYearAccountingPeriodScenarioPeriodTypeAmountPosted date
20211ActualYTD$1001/31/2021
20212ActualYTD$2002/28/2021
20213ActualYTD$3003/31/2021

 

Your help is much appricicated. 

 

Thank you in advance!

ISGirl

3 ACCEPTED SOLUTIONS
TheoC
Memorable Member
Memorable Member

@ISGirl Are the amounts in your table already cumulative amounts (i.e. there is no column that specifies the amount that each month had against it)?  If you have a monthly amount, you should be able to just drag the Amount field into a visual with the Period field without any measure.

 

TheoC_0-1634681615564.png

 

I may be misunderstanding what you are after? Apologies on my end.

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

View solution in original post

TheoC
Memorable Member
Memorable Member

 I am so sorry for the misunderstanding! That is my bad.  This should help  

Monthly Amount = TOTALMTD ( SUM ( 'Table'[Amount] ) , 'Table'[Posted Date] ) - CALCULATE ( SUM ( 'Table'[Amount] ) , DATEADD ( 'Table'[Posted Date] , -1 , MONTH ) , ALL ( 'Table' ) )
 
TheoC_0-1634685026396.png

The above can be broken down using variables as follows:

 

Monthly Amount = 

VAR _TotalMth = TOTALMTD ( SUM ( 'Table'[Amount] ) , 'Table'[Posted Date] )
VAR _TotalLstMth = CALCULATE( [Sum Amount] , DATEADD ( 'Table'[Posted Date] , -1 , MONTH ) , ALL ( 'Table' ) )

RETURN 

_TotalMth - _TotalLstMth

 

Hope this helps and apologies again!

 

Theo 🙂

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

View solution in original post

ChrisMendoza
Super User
Super User

@ISGirl -

You might need some more tweaking if you have additional data but the example could serve as a template

Distributed Amount = 
DIVIDE(
    CALCULATE(
        MAX(TableName[Amount]),
        FILTER(
            ALL(TableName),
            TableName[FiscalYear]
        )
    ),
    CALCULATE(
        COUNTROWS(TableName),
        FILTER(
            ALL(TableName),
            TableName[FiscalYear]
        )
    )
)

image.png






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



View solution in original post

12 REPLIES 12
v-shex-msft
Community Support
Community Support

Hi @ISGirl,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
ChrisMendoza
Super User
Super User

@ISGirl -

You might need some more tweaking if you have additional data but the example could serve as a template

Distributed Amount = 
DIVIDE(
    CALCULATE(
        MAX(TableName[Amount]),
        FILTER(
            ALL(TableName),
            TableName[FiscalYear]
        )
    ),
    CALCULATE(
        COUNTROWS(TableName),
        FILTER(
            ALL(TableName),
            TableName[FiscalYear]
        )
    )
)

image.png






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



View solution in original post

TheoC
Memorable Member
Memorable Member

Hi @ISGirl 

 

Please use the below measure:

 

Cumulative Sum = TOTALYTD ( SUM ( 'Table'[Amount] ) , 'Table'[Posted Date] )

 

Hope this helps 🙂


Theo

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

I need calculation that would return the oppoiste result 🙂

Jan =$100

Feb=$100

Mar=$100

TheoC
Memorable Member
Memorable Member

@ISGirl my apologies, you should just be able to drag the Amount column without a measure if you are just after the monthly amount total?  

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

It still showing cumulative. Thank you so much for your help!

TheoC
Memorable Member
Memorable Member

@ISGirl Are the amounts in your table already cumulative amounts (i.e. there is no column that specifies the amount that each month had against it)?  If you have a monthly amount, you should be able to just drag the Amount field into a visual with the Period field without any measure.

 

TheoC_0-1634681615564.png

 

I may be misunderstanding what you are after? Apologies on my end.

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

View solution in original post

I don't have monthly amount (only for January), the rest of months are cumulative. So I need formula calculating monthly amount based on cumulative. 

TheoC
Memorable Member
Memorable Member

 I am so sorry for the misunderstanding! That is my bad.  This should help  

Monthly Amount = TOTALMTD ( SUM ( 'Table'[Amount] ) , 'Table'[Posted Date] ) - CALCULATE ( SUM ( 'Table'[Amount] ) , DATEADD ( 'Table'[Posted Date] , -1 , MONTH ) , ALL ( 'Table' ) )
 
TheoC_0-1634685026396.png

The above can be broken down using variables as follows:

 

Monthly Amount = 

VAR _TotalMth = TOTALMTD ( SUM ( 'Table'[Amount] ) , 'Table'[Posted Date] )
VAR _TotalLstMth = CALCULATE( [Sum Amount] , DATEADD ( 'Table'[Posted Date] , -1 , MONTH ) , ALL ( 'Table' ) )

RETURN 

_TotalMth - _TotalLstMth

 

Hope this helps and apologies again!

 

Theo 🙂

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

View solution in original post

TheoC
Memorable Member
Memorable Member

@ISGirl can you kindly accept the solution I put forward as "solution" so as to close the topic? Many thanks! Theo

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

Thank you so much! It works perfectly fine for 2021. But if I have 2019 and 2020, do you think it will work?

TheoC
Memorable Member
Memorable Member

Hi @ISGirl, absolutely 🙂

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.