# Power BI Exchange

View Only

## Current year cumulative with forecast & last year cumulative

• #### 1.  Current year cumulative with forecast & last year cumulative

Gold Contributor
Posted Jun 26, 2022 04:35 AM
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

------------------------------
Data Analyst
Karachi
------------------------------

Attachment(s)

cumulative example.pbix   83 KB 1 version
Cumulative example.xlsx   14 KB 1 version

• #### 2.  RE: Current year cumulative with forecast & last year cumulative

Top Contributor
Posted Jun 26, 2022 06:30 PM
Edited by Nicolas MENDEZ Jun 26, 2022 06:31 PM
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
------------------------------