Semi additive last balance to close date

[mark_carlisle]

I have the following table...

I have used the following guide Semi-additive calculations – DAX Patterns to create a measure to show me the last forecast_amount_average_contract_value by pipeline_journey_id up to the forecast close date.

Average Contract Value | ACV - Forecast Use Only = 
VAR __max_date =
    MAX ( dim_date[date] )

VAR __max_balance_dates =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE ( fact_pipeline_events_forecast, fact_pipeline_events_forecast[pipeline_journey_id] ),
            "@MaxBalanceDate", CALCULATE ( MAX ( fact_pipeline_events_forecast[forecast_event_date] ) )
        ),
        dim_date[date] <= __max_date,
        fact_pipeline_events_forecast[forecast_close_date] >= __max_date
    )
VAR __max_balance_dates_with_lineage =
    TREATAS ( __max_balance_dates, fact_pipeline_events_forecast[pipeline_journey_id], dim_date[date] )
VAR __result =
    CALCULATE ( SUM ( fact_pipeline_events_forecast[forecast_amount_average_contract_value] ), __max_balance_dates_with_lineage )
RETURN
    __result

This just about does what I want it to, in that it projects up to the close date

But this projects the ACV to the max close date. I want it to return the following 

This is based on the latest close date for each journey, rather than the max close date for each journey.

How can I amend the measure or the table, through a calculated column, to achieve this?

[Anonymous]

Hi @mark_carlisle ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a measure. 

Average Contract Value | ACV - Forecast Use Only = 
VAR MaxBalanceDates =
    ADDCOLUMNS (
        SUMMARIZE (          
           'fact_pipeline_events_forecast',         
            'fact_pipeline_events_forecast'[pipeline_journey_id]
        ),
        "@MaxBalanceDate", CALCULATE (    
            MAX ( fact_pipeline_events_forecast[forecast_event_date]  )        
        )
    )
VAR MaxBalanceDatesWithLineage =
    TREATAS (                
        MaxBalanceDates,     
         'fact_pipeline_events_forecast'[pipeline_journey_id],      
        'dim_date'[Date]        
    )
VAR Result =
    CALCULATE (
        SUM ('fact_pipeline_events_forecast'[forecast_amount_average_contract_value]),
        MaxBalanceDatesWithLineage
    )
RETURN
    Result

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[mark_carlisle]

Thanks for the response @Anonymous. However the solution is incomplete. I've adjusted the dates some of the events so theres no data for a pipeline_journey_id in some months. See the values highlighted below for changes.

This gives the following.

But I want to achieve the following

So for months where theres no data the last known value needs to be populated.