Calculate Monthly based on Cumulative Total

[ISGirl]

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

FiscalYear	AccountingPeriod	Scenario	PeriodType	Amount	Posted date
2021	1	Actual	YTD	$100	1/31/2021
2021	2	Actual	YTD	$200	2/28/2021
2021	3	Actual	YTD	$300	3/31/2021

Your help is much appricicated. 

Thank you in advance!

ISGirl

[TheoC · Accepted solution]

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

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

[TheoC · Accepted solution]

 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' ) )

 

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 🙂

[v-shex-msft]

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

[ChrisMendoza · Accepted solution]

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

[TheoC]

Hi @ISGirl 

Please use the below measure:

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

Hope this helps 🙂

Theo

[ISGirl]

I need calculation that would return the oppoiste result 🙂

Jan =$100

Feb=$100

Mar=$100

[TheoC]

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

[ISGirl]

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

[TheoC · Accepted solution]

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

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

[ISGirl]

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 · Accepted solution]

 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' ) )

 

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 🙂

[TheoC]

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

[ISGirl]

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

[TheoC]

Hi @ISGirl, absolutely 🙂