Split a value over next 12 months

[GRedhead]

Hi,

I have a value e.g. 12,000 that i need to split over 12 months form a date i.e. 1/4/2022 - 1/3/2023 evenly so 1,000 per month and show in a table.

If there are two 1,000s in a month i need these to be totaled too, to create something like the following.

	April	May	June	July	August	September
Opp 1 (Start Date in April)	1000	1000	1000	1000	1000	1000
Opp 2 (start date in June)			1000	1000	1000	1000
Total	1000	1000	2000	2000	2000	2000

As i will then use the totals to produce a line graph that will always increase month on month.

[Aucesar]

Hi @v-xiaotang 

I´m on the same boat, trying to adapt you example to:

OPP1 =

var _start=min(BI_LancContabil[dDatLanc]) //(this is a column with entry date)

var _value=min(BI_LancContabil[nVlrLanc]) //(this is a column with the value I need to split)

var _end=EOMONTH(_start,11) //(Nothing to change here)

return IF('Calendario'[date]>=_start&&'Calendario'[date]<_end && DAY('Calendario'[date]=1,_value/12))

My Calendar table is called "Calendario" (Portuguese) created with this DAX, I getting error when I change

IF('Date'[Date]>=_start&&'Date'[Date]<_end && DAY('Date'[Date])=1,_value/12)

To

IF('Calendario'[Date]>=_start&&'Calendario'[Date]<_end && DAY('Calendario'[Date])=1,_value/12)

I´m getting "Cannot find Date" but it exists.

Calendario =

ADDCOLUMNS (

CALENDAR (DATE(2000,1,1), DATE(2030,12,31)),

"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),

"Year", YEAR ( [Date] ),

"Monthnumber", FORMAT ( [Date], "MM" ),

"MonthDay", FORMAT ( [Date], "DD/MMM" ),

"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),

"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),

"MonthNameShort", FORMAT ( [Date], "mmm" ),

"MonthNameLong", FORMAT ( [Date], "mmmm" ),

"DayOfWeekNumber", WEEKDAY ( [Date] ),

"DayOfWeek", FORMAT ( [Date], "dddd" ),

"DayOfWeekShort", FORMAT ( [Date], "ddd" ),

"DayOfMonth", FORMAT ( [Date], "DD" ),

"Quarter", "Q" & FORMAT ( [Date], "Q" ),

"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )

)

If you need more info please tell me. Thanks in advance

[v-xiaotang · Accepted solution]

Hi @GRedhead 

You can try this, 

create a calendar table

Date = CALENDAR(DATE(2022,4,1),DATE(2023,7,1))

create a YM column, 

YM = FORMAT('Date'[Date],"yyyy-mm")

create 2 colums [Opp 1] & [Opp 2],

OPP1 = 
var _start=DATE(2022,4,1)
var _value=12000
var _end=EOMONTH(_start,11)
return IF('Date'[Date]>=_start&&'Date'[Date]<_end && DAY('Date'[Date])=1,_value/12)

OPP2 = 
var _start=DATE(2022,6,1)
var _value=12000
var _end=EOMONTH(_start,11)
return IF('Date'[Date]>=_start&&'Date'[Date]<_end && DAY('Date'[Date])=1,_value/12)

create a total measure

Measure = MIN('Date'[OPP1])+MIN('Date'[OPP2])

result

I also find 2 posts related for your reference,

https://community.powerbi.com/t5/Desktop/Splitting-values-equally-into-Months-based-on-the-Start-and...

https://community.powerbi.com/t5/Desktop/Splitting-period-data-into-months/m-p/113861

hope they will help.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[GRedhead]

Hi, I am trying to generate the following table and chart from the raw data circled, any assistance on how I can do this will be most appreciated.

The data in the raw table is basically opportunity values and we have a start date, I need to add up the total each month and then evenly split the month's value out over the following 12 months and produce the lower table and chart.

[Greg_Deckler]

@GRedhead Right, so divide by 12 and then handle the measure total issue. This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[GRedhead]

Hi, @Greg_Deckler I am trying to generate the following table and chart from the raw data circled, any assistance on how I can do this will be most appreciated.

The data in the raw table is basically opportunity values and we have a start date, I need to add up the total each month and then evenly split the month's value out over the following 12 months and produce the lower table and chart.