Forum Discussion
Helper ISplit a value over next 12 months
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.
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-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.
5 Replies
v-xiaotangCommunity Support
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-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.
GRedheadHi, 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
Community Champion
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.
- Aucesar
Helper III
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 changeIF('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
