Forum Discussion
Helper ISplit a value over next 12 months
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.
Community ChampionGRedhead 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
Helper IHi, 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.