Forum Discussion
kristel_tulio
Helper III
Helper IIIFull Year Projection
Hi, I would like to ask your help in getting Full year projection = Current year actuals for selected months + prior year actuals for the months which have not been selected. I have this measure "...
some_bih
Community Champion
Community ChampionHi kristel_tulio I created Excel from your picture data and following measures:
Adjust "Sheet9" to your table
1. M_P&L = SUM(Sheet9[P&L Value])
2. P&L_MTD =
CALCULATE([M_P&L],
DATESMTD('Date'[Date]
)
)
3. P&L_YTD =
CALCULATE([M_P&L],
DATESYTD('Date'[Date]
)
)
4. M_MTD_Previous_Year =
CALCULATE([M_P&L],
CALCULATETABLE(
DATEADD('Date'[Date],-12,MONTH)
)
)
5. Full Year Projection =
VAR _ytd_pl=[P&L_YTD]
VAR _mtd_pl=[P&L_MTD]
VAR _mtd_prev_year=[M_MTD_Previous_Year]
VAR _previousmonth=max(Sheet9[DatesFullYearProjection])
VAR _Result=
IF(
ISBLANK(_mtd_pl),
_ytd_pl+_mtd_prev_year,
_ytd_pl
)
RETURN _Result
I hope this is ok for you.
kristel_tulioHelper III
Helper III