Dynamic calculation of YTD and ETC based on the se…

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:  

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

3.        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] 
            ) 
        ) 
    ) 

 

  

4.        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] ) 
    ) 
) 

 

5.        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