Forum Discussion
GRedhead
Helper I
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...
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.
Aucesar
Helper III
Helper IIIHi 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('Calendario'[Date]>=_start&&'Calendario'[Date]<_end && DAY('Calendario'[Date])=1,_value/12)
I´m getting "Cannot find Date" but it exists.
If you need more info please tell me. Thanks in advance
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)ToIF('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