Need help on cumulative for months

[Anonymous]

Hello Experts,

Need your expertise help on power bi - Excel ( cumulative for months calculations). Tried all ways, but not able to get for all divisions.

Please help me

I have uploaded excel file. need your help.

Column A to Column E are inputs for power bi

need output like mentioned in excel

Three formulas need to implement to get final output.

Any help is highly appreciated.

https://www.dropbox.com/scl/fi/0zv8tic9li90en071sksc/example.xlsx?dl=0&rlkey=6qyefd5p5kdt4fpthfth2gm...

Thanks,

Rajendra

[Anonymous]

Hi @lbendlin ,

Thanks a lot for your help. I am almost near by. having below issues. Your inputs might help me.

1. I forgot to mention, there is region field, where I need to put in filter and I need to exclude two regions. Shall I write "Region" field beside division in formula.

2. When I drag month (jan) or monthnumber (1) into my table, below error throwing. Please help.

"calculation error in measure (result); the result of a conversion or arithmetic operation is either too large or too small"

[lbendlin · Accepted solution]

1. Yes

2. your previous sample data didn't need protection against division by zero.  Change the formula to

Result = 
var m = max('Source File'[Month Number])
var dc1 = CALCULATE(sum('Source File'[DistCosts]),'Source File'[Year]="2021", 'Source File'[Month Number] = m)
var dc0 = CALCULATE(sum('Source File'[DistCosts]),'Source File'[Year]="2020", 'Source File'[Month Number] = m)
var is0 = CALCULATE(sum('Source File'[Invoice Sales]),'Source File'[Year]="2020", 'Source File'[Month Number] = m)
var is1 = CALCULATE(sum('Source File'[Invoice Sales]),'Source File'[Year]="2021", 'Source File'[Month Number] = m)
var dc0p = CALCULATE(sum('Source File'[DistCosts]),ALLEXCEPT('Source File','Source File'[Division],'Source File'[Region]),'Source File'[Year]="2020",'Source File'[Month Number] <m)
var is0p =CALCULATE(sum('Source File'[Invoice Sales]),ALLEXCEPT('Source File','Source File'[Division],'Source File'[Region]),'Source File'[Year]="2020",'Source File'[Month Number] <m)
var is1p = CALCULATE(sum('Source File'[Invoice Sales]),ALLEXCEPT('Source File','Source File'[Division],'Source File'[Region]),'Source File'[Year]="2021",'Source File'[Month Number] <m)
return (divide(dc0p+dc0,is0p+is0,0)*(is1p+is1)-dc1-divide(dc0p,is0p,0)*is1p)/1000

[Anonymous]

Thank you so much @lbendlin .

You are genius and awesome. Thanks again.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. 

Please show the expected outcome based on the sample data you provided. 

[Anonymous]

Thanks for your time.

here is my PBI file and source file.

PBI - https://www.dropbox.com/s/kpjbz9vvd50znhj/source.pbix?dl=0

Source: https://www.dropbox.com/scl/fi/9zaxoctpi2tuxls3irijn/source.xlsx?dl=0&rlkey=72f50ncxc5c962jdiseo9j1u...

[Ashish_Mathur]

Hi,

Clearly show how you arrived at the numbers on the Result worksheet. 

[lbendlin]

Here is a possible implementation

Note that I interpreted some of your description as there are inconsistencies. I also added a sort column ("Date") for your month names. See attached.

[lbendlin]

Slightly more efficient version:

Result = 
var m = max('Table'[Date])
var dc1 = CALCULATE(sum('Table'[DC]),'Table'[year]="2021",'Table'[Date]=m)
var dc0 = CALCULATE(sum('Table'[DC]),'Table'[year]="2020",'Table'[Date]=m)
var is0 = CALCULATE(sum('Table'[IS]),'Table'[year]="2020",'Table'[Date]=m)
var is1 = CALCULATE(sum('Table'[IS]),'Table'[year]="2021",'Table'[Date]=m)
var dc0p = CALCULATE(sum('Table'[DC]),ALLEXCEPT('Table','Table'[Division]),'Table'[year]="2020",'Table'[Date]<m)
var is0p = CALCULATE(sum('Table'[IS]),ALLEXCEPT('Table','Table'[Division]),'Table'[year]="2020",'Table'[Date]<m)
var is1p = CALCULATE(sum('Table'[IS]),ALLEXCEPT('Table','Table'[Division]),'Table'[year]="2021",'Table'[Date]<m)
return ((dc0p+dc0)/(is0p+is0)*(is1p+is1)-dc1-dc0p/is0p*is1p)/1000

[Anonymous]

Thank you @lbendlin .

You are awesome. Let me try in my dashboard and get back to you.

Thanks again for your wonderful formula.