CALCULATETABLE to Iterate Revenue and Expenses over Monthly Periods using Start and End Dates.

[Anonymous]

Hello,

I am trying to CALCULATETABLE (sample below) to iterate through the FIRSTDATE (START_DATE) and LASTDATE (END_DATE) and straight-line REVENUE and LABOR over each monthly period so I can tie the proposed project data to monthly actuals to a date dimension table.

PROPOSED PROJECT DATA

PROJ_ID     START_DATE    END_DATE     REVENUE     LABOR

11111         8/15/2020        8/14/2021      1,000,000    750,000

11111         8/15/2021        8/14/2022      1,000,000    750,000

11112         8/15/2020        8/14/2021      1,000,000    750,000

11113         8/15/2020        8/14/2021      1,000,000    750,000

I thank you in advanced. 

[v-rzhou-msft]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[Anonymous]

@v-rzhou-msft Thank you and it is VERY close.  

One last item:  In the example that you provided (which is awesome, BTW) each project iterates through the MIN and MAX Period of Performance (POP)  Sart and POP End Dates rather than the projects individual start and end dates. Is there a way to calculate the table by each project's start and end date?

As an example, project 25011 should only have 6 line items:

Project Code POP Start POP End Number of Lines to Average Revenue and Labor
11139 10/1/2014 9/30/2015 11
15147 7/21/2014 7/21/2015 12
15160 2/28/2015 2/15/2016 12
19031 2/16/2015 2/15/2016 12
19032 3/9/2015 3/8/2016 12
24005 7/1/2015 6/30/2016 11
25011 5/1/2015 11/30/2015 6
36007 5/1/2015 9/30/2016 16

I appreciate all the help! 

Brad 

[Anonymous]

@Ashish_Mathur @v-rzhou-msft @AntrikshSharma 

Using project 11113 as an example, the results would be: 

PROJECT_ID      PERIOD         REVENUE LABOR
11113                9/18/2018     83,333     62,500
11113                10/18/2018   83,333     62,500
11113                11/17/2018   83,333     62,500
11113                12/17/2018   83,333     62,500
11113                1/16/2019    83,333      62,500
11113                2/15/2019    83,333      62,500
11113                3/17/2019    83,333      62,500
11113                4/16/2019    83,333      62,500
11113                5/16/2019    83,333      62,500
11113                6/15/2019    83,333      62,500
11113                7/15/2019    83,333      62,500
11113                8/14/2019    83,333      62,500

Hope this helps!

[v-rzhou-msft]

Hi @Anonymous 

I build a table like yours to have a test.

Build a Period for 11111 table.

Build two measure:

REVENUE = 
VAR _Re =
    CALCULATE (
        SUM ( 'PROPOSED PROJECT DATA'[REVENUE] ),
        FILTER (
            'PROPOSED PROJECT DATA',
            'PROPOSED PROJECT DATA'[START_DATE] <= MAX ( 'Period for 11111'[Period] )
                && 'PROPOSED PROJECT DATA'[END_DATE] >= MAX ( 'Period for 11111'[Period] )
                && 'PROPOSED PROJECT DATA'[PROJ_ID] = MAX ( 'Period for 11111'[ID] )
        )
    )
RETURN
    DIVIDE ( _Re, 12 )

LABOR = 
VAR _LA =
    CALCULATE (
        SUM ( 'PROPOSED PROJECT DATA'[LABOR] ),
        FILTER (
            'PROPOSED PROJECT DATA',
            'PROPOSED PROJECT DATA'[START_DATE] <= MAX ( 'Period for 11111'[Period] )
                && 'PROPOSED PROJECT DATA'[END_DATE] >= MAX ( 'Period for 11111'[Period] )
                && 'PROPOSED PROJECT DATA'[PROJ_ID] = MAX ( 'Period for 11111'[ID] )
        )
    )
RETURN
    DIVIDE ( _LA, 12 )

Result:

You can download the pbix file from this link: CALCULATETABLE to Iterate Revenue and Expenses over Monthly Periods using Start and End Date

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

@v-rzhou-msft I'm close! The below code is skipping a month.  It should iterate over a 60 month period but I'm getting 31. The average revenue over the table equals total revenue!  You have been a great help/inspiration.  All your help has been greatly appreciated. 

CLOSE =

VAR baseTable =

    DISTINCT( ( 'Post Award Tracking v2'[Project Code] ) )

RETURN

    GENERATE (

        baseTable,

        VAR PoP_Start = [PoP_Start] //MIN

        VAR PoP_End = [PoP_End] //MAX

        VAR Months =

            DATEDIFF ( PoP_Start, PoP_End, MONTH ) -- calc number of months for the project

        VAR Months_Offset =

            DATEDIFF ( PoP_Start, PoP_End, MONTH ) -- didn't work

        VAR Avg_Rev =

            DIVIDE ( CALCULATE ( SUM ( 'Post Award Tracking v2'[Revenue] ) ), Months )

        VAR Revenue =

            CALCULATE ( SUM ( 'Post Award Tracking v2'[Revenue] ) )

        RETURN

            GENERATE (

                GENERATESERIES ( PoP_Start, PoP_End,Months ), -- this is the part I'm having trouble with, skipping a month

                VAR inc = [Value]

                RETURN

                ROW (

                    //"Proj_ID", baseTable,

                    "PoP Start", PoP_Start,

                    "PoP End", PoP_End,

                    "Months", Months,

                    "Avg Rev", Avg_Rev,

                    "Revenue", Revenue

                )

            )

    )

Result: 

(can't figure out how to add images :()

PBI File 

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

I build a table like yours and add a calculated column in it.

Rev & Labor = 
VAR baseTable =
    DISTINCT ( ( 'Post Award Tracking v2'[Project Code] ) )
RETURN
    GENERATE (
        baseTable,
        VAR PoP_Start =
            MIN ( 'Post Award Tracking v2'[POP Start] )
        VAR PoP_End =
            MAX ( 'Post Award Tracking v2'[POP End] )
        VAR MonthDiff =
            DATEDIFF ( PoP_Start, PoP_End, MONTH )
        VAR Avg_Rev =
            DIVIDE (
                CALCULATE ( SUM ( 'Post Award Tracking v2'[Revenue_TEST] ) ),
                MonthDiff
            )
        VAR Revenue =
            CALCULATE ( SUM ( 'Post Award Tracking v2'[Revenue_TEST] ) )
        VAR Avg_LABOR =
            DIVIDE ( CALCULATE ( SUM ( 'Post Award Tracking v2'[Labor_TEST] ) ), MonthDiff )
        VAR Labor =
            CALCULATE ( SUM ( 'Post Award Tracking v2'[Labor_TEST] ) )
        RETURN
            GENERATE (
                GENERATESERIES ( PoP_Start, PoP_End, 1 ),
                VAR inc = [Value]
                VAR _Month =
                    MONTH ( [Value] )
                RETURN
                    ROW (
                        "PoP Start", PoP_Start,
                        "PoP End", PoP_End,
                        "Months", MonthDiff,
                        "Avg Rev", Avg_Rev,
                        "Revenue", Revenue,
                        "Avg Labor", Avg_LABOR,
                        "Labor", Labor,
                        "Month", _Month
                    )
            )
    )

Min each month date = 
IF (
    AND (
        MONTH ( 'Rev & Labor'[Value] ) = MONTH ( 'Rev & Labor'[PoP Start] ),
        YEAR ( 'Rev & Labor'[Value] ) = YEAR ( 'Rev & Labor'[PoP Start] )
    ),
    'Rev & Labor'[PoP Start],
    CALCULATE (
        MIN ( 'Rev & Labor'[Value] ),
        FILTER (
            'Rev & Labor',
            'Rev & Labor'[Month] = EARLIER ( 'Rev & Labor'[Month] )
                && YEAR ( 'Rev & Labor'[Value] ) = EARLIER ( 'Rev & Labor'[Value].[Year] )
        )
    ) + 20
)

Build a Table Visual to show the result:

You can download the pbix file from this link: CALCULATETABLE to Iterate Revenue and Expenses over Monthly Periods using Start and End Dates.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

Hello @v-rzhou-msft!

One last item:  In the example that you provided (which is awesome, BTW) each project iterates through the MIN and MAX Period of Performance (POP)  Sart and POP End Dates rather than the projects individual start and end dates. Is there a way to calculate the table by each project's start and end date?

As an example, project 25011 should only have 6 line items:

Project Code POP Start POP End Number of Lines to Average Revenue and Labor
11139 10/1/2014 9/30/2015 11
15147 7/21/2014 7/21/2015 12
15160 2/28/2015 2/15/2016 12
19031 2/16/2015 2/15/2016 12
19032 3/9/2015 3/8/2016 12
24005 7/1/2015 6/30/2016 11
25011 5/1/2015 11/30/2015 6
36007 5/1/2015 9/30/2016 16

I appriciate all the help! 

Brad 

[Anonymous]

@v-rzhou-msft 

Thank you so much!  It is close... Is there a way to do it dynamically? I know it’s kind of sloppy, but the attempted code is below.  Can I use the GENERATESERIES based on start and end columns? Also, rather than measures, I would like to calculate a table for others to use.

Sample

Project Code   Project Name              POP Start        POP End                     Revenue_TEST            Labor_TEST

15147              SATCOM San               7/21/2014      7/20/2015                 $1,000,000                   $750,000

11139              WAPA Dept Of           10/1/2014       9/30/2015                  $1,000,000                   $750,000

19031              NAVAIR 7.0                 2/16/2015      2/15/2016                  $1,000,000                    $750,000

15160              Kwaj Security and        2/28/2015     2/15/2016                  $1,000,000                    $750,000

19032              Logistics Analysis         3/9/2015       3/8/2016                    $1,000,000                    $750,000

25011              Oak Ridge - DOE          5/1/2015      11/30/2015                 $1,000,000                    $750,000

36007              NASA Lab Services       5/1/2015       9/30/2016                   $1,000,000                   $750,000

24005              Fusion Center IDS        7/1/2015       6/30/2016                  $1,000,000                    $750,000

 TEST =

VAR baseTable =

    CALCULATETABLE ( SUMMARIZECOLUMNS ( 'Post Award Tracking v2'[Project Code] ) )

RETURN

    GENERATE (

        baseTable,

        VAR Months =

            DATEDIFF ( [POP Start Measure], [POP End Measure], DAY )

        VAR Start_Date = [POP Start Measure]

        VAR End_Date = [POP End Measure]

        VAR AvgRev =

            DIVIDE ( [Revenue_Measure], Months, 0 )

        VAR Revenue = [Revenue_Measure] //VAR GenSeries = GENERATESERIES([POP Start Measure],[POP End Measure],Months)

        RETURN

            ROW (

                "Start", Start_Date,

                "End", End_Date,

                "Months", Months,

                "Avg Rev", AvgRev,

                "Revenue", Revenue

            )

    )

Thank you so much for your help!

[Ashish_Mathur]

Hi,

I cannot understand your result.  How have you arrived at the Date in the Period column and the numbers in the last 2 columns?

[Ashish_Mathur]

Hi,

Show the expected result with an explanation.

[v-rzhou-msft]

Hi @Anonymous 

Could you provide me more details about your calculate logic? You may show me the result you want the table you may need to use and the relationships you build. Or you can share me your pbix file by your Onedrive for Business.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[AntrikshSharma]

It is not clear what you are trying to do, try and spend more time to explain your requirement with more details.