cancel
Showing results for 
Search instead 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

1.png

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

2.png

3)        Create a conditional column to get month number 

3.png

The related applied codes as follows:  

4.png

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

5.png

  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 .

6.png

 

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