Hello,
Here is my solution
To get the result I wrote 3 measures :
YTD TY =
VAR _Year = MAXX( ALL('Calendar Table'[Year]), 'Calendar Table'[Year])
RETURN
CALCULATE(
[YTD],
'Calendar Table'[Year] = _Year
)
YTD LY =
VAR _Year = MAXX( ALL('Calendar Table'[Year]), 'Calendar Table'[Year]) - 1
RETURN
CALCULATE(
[YTD],
'Calendar Table'[Year] = _Year
)
20 % Forecast YTD =
VAR _CurrMonth = SELECTEDVALUE( 'Calendar Table'[MonthOfYear] )
VAR _LastMonth = MONTH( LASTNONBLANK( ALL('Calendar Table'[Date]), [Total Budget] ) )
VAR _MaxYTDCurrYear = CALCULATE( [YTD], 'Calendar Table'[MonthOfYear] = _LastMonth )
VAR _MaxYTDLastYear = CALCULATE( [YTD LY], 'Calendar Table'[MonthOfYear] = _LastMonth)
VAR _YTDLastYear =
CALCULATE(
[YTD LY],
FILTER(
ALL('Calendar Table'[MonthOfYear]),
'Calendar Table'[MonthOfYear] > _LastMonth && 'Calendar Table'[MonthOfYear] <= _CurrMonth
)
)
RETURN
_MaxYTDCurrYear +
IF(
_CurrMonth > _LastMonth ,
_YTDLastYear - _MaxYTDLastYear
) * 0.8
Should work ... I hope this is a good start for you....
------------------------------
Nicolas MENDEZ
Data & BI consultant
Becom Consulting
------------------------------
Original Message:
Sent: Jun 26, 2022 04:35 AM
From: Khurram Muhammad
Subject: Current year cumulative with forecast & last year cumulative
I am trying to achieve the same cumulative result as I mentioned in attached snapshot.
Sample file is also attached. Please calculate a measures and send back to me.
Thank you
------------------------------
Khurram Muhammad
Data Analyst
Karachi
------------------------------