cancel
Showing results for
Did you mean:

## Dynamic calculation of YTD and ETC based on the selected month

Scenario:

Every month user will receive a data file containing one year’s actual expense and forecast expense. For example, in August, this data file will contain the actual expenses from January to August and the updated forecast expenses from January to December. Examples of data are as follows:

 Subservice Actual January Actual February … Actual December Forecast January Forecast February Forecast March … Forecast December SubService 1 396,53 396,53 … 0,00 0,00 0,00 0,00 ... 4361,59 SubService 2 6188,07 4073,16 … 0,00 306,58 306,58 306,58 ... 4568,12

Expected result:

Consider current month as August, we would need to follow the calculation logic to design the report, so that it would display like the table below:

YTD=Actual January + Actual February + Actual March + Actual April + Actual May + Actual June + Actual July + Actual August

ETC=Forecast September + Forecast October + Forecast November + Forecast December

EAC = YTD + ETC

 Subservice Actual January Actual February … Actual August Forecast September … Forecast December SubService 1 396,53 396,53 … 6174,50 500,00 … 4361,59 SubService 2 6188,07 4073,16 … 2038,56 980,06 … 4568,12

Detailed steps:

1.       Make some transformations on the original data in Query Editor to facilitate subsequent calculations

1)        Split the [Actual XXX] and [Forecast XXX] columns into expense type column (Attribute.1) and month column (Attribute.2) by unpivot and split

2)        Then split the expense type column (Attribute.1) into actual expense and forecast expense column by pivot

3)        Create a conditional column to get month number

The related applied codes as follows:

1.        Create a month table : not create any relationship with the original expense table), the Month filed will apply on the slicer

1.        Create measures to obtain actual expense and forecast expense dynamically according to the selected month

``````Nactual =
CALCULATE (
MAX ( 'Table'[Actual] ),
FILTER (
'Table',
'Table'[Month Number] <= SELECTEDVALUE ( 'Months'[Month Number] )
)
) ``````

``````Nforecast =
CALCULATE (
MAX ( 'Table'[Forecast] ),
FILTER (
'Table',
'Table'[Month Number] > SELECTEDVALUE ( 'Months'[Month Number] )
)
) ``````

``````Measure =
VAR _selmonth =
SELECTEDVALUE ( 'Months'[Month Number] )
RETURN
SUMX (
VALUES ( 'Table'[Subservice] ),
SUMX (
VALUES ( 'Table'[Month Number] ),
IF (
'Table'[Month Number] > _selmonth,
[Nforecast],
[Nactual]
)
)
) ``````

1.        Create measures to obtain YTD, ETC and EAC

``````YTD =
CALCULATE (
SUM ( 'Table'[Actual] ),
FILTER (
'Table',
'Table'[Month Number] <= SELECTEDVALUE ( 'Months'[Month Number] )
)
) ``````

``````ETC =
CALCULATE (
SUM ( 'Table'[Forecast] ),
FILTER (
'Table',
'Table'[Month Number] > SELECTEDVALUE ( 'Months'[Month Number] )
)
) ``````

 EAC = [YTD]+[ETC]
1.        Sorting for month name field

Ø  Select Month field in Table from the Fields pane and select Column tools Sort by Column > select Month Number .

You can find all details in the attached file. Hope this article helps everyone with similar questions here.

Author: Yingying Ruan

Reviewer: Ula Huang, Kerry Wang

Top Kudoed Posts
Latest Articles
Archives