Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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) 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:
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]
)
)
)
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] |
|
Ø 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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.