Dax to choose which measure to calculate based on Boolean field condition

[steph_io]

I need to plot cumulative forecasted values - when I have these forecasted values available in my table

otherwise I take a 3Month running average of the actual values

Here are sample tables; 

Actual Costs				Forecasted Costs
Product	Date	Actual		Product	Date	Forecast
A	2018-11-01	$  100.00		A	2019-02-01	$  120.00
A	2018-12-01	$  110.00		A	2019-03-01	$  120.00
A	2019-01-01	$  120.00		A	2019-04-01	$  120.00
B	2018-12-01	$  200.00		C	2019-02-01	$  200.00
B	2019-01-01	$  210.00		C	2019-03-01	$  200.00
C	2018-12-01	$  200.00		C	2019-04-01	$  210.00
C	2019-01-01	$  210.00

I created a column in my Product table that tells me if a Product has forecasted values.  

Forecastexists = CONTAINS('Forecast','Forecast'[Product], Product[Product])

I need a formula that calculates cumulative sum when Forecastexists = true, else it's the cumulative Running 3M Average of Actuals....

so that it plots as a continuous line in the visual. 

[v-frfei-msft · Accepted solution]

Hi @steph_io ,

One sample for your reference.

1. Create a date table and create relstionship between it and the actual table.

Table = CALENDARAUTO()

2. To create a calculated column as below.

Column = 
VAR _result =
    LOOKUPVALUE (
        'Forecasted Costs'[Forecast],
        'Forecasted Costs'[Date], DATEADD ( 'Table'[Date], 3, MONTH ),
        'Forecasted Costs'[Product ], 'Actual Costs'[Product]
    )
VAR _rows =
    CALCULATE (
        COUNTROWS ( 'Actual Costs' ),
        FILTER (
            'Actual Costs',
            'Actual Costs'[Date] <= EARLIER ( 'Actual Costs'[Date] )
                && 'Actual Costs'[Forecastexists] = FALSE ()
        )
    )
RETURN
    IF (
        ISBLANK ( _result ),
        CALCULATE (
            SUM ( 'Actual Costs'[Actual] ),
            FILTER (
                'Actual Costs',
                'Actual Costs'[Date] <= EARLIER ( 'Actual Costs'[Date] )
                    && 'Actual Costs'[Forecastexists] = FALSE ()
            )
        ) / _rows,
        _result
    )

Please check the pbix as attached.

Regards,

Frank

[v-frfei-msft · Accepted solution]

Hi @steph_io ,

One sample for your reference.

1. Create a date table and create relstionship between it and the actual table.

Table = CALENDARAUTO()

2. To create a calculated column as below.

Column = 
VAR _result =
    LOOKUPVALUE (
        'Forecasted Costs'[Forecast],
        'Forecasted Costs'[Date], DATEADD ( 'Table'[Date], 3, MONTH ),
        'Forecasted Costs'[Product ], 'Actual Costs'[Product]
    )
VAR _rows =
    CALCULATE (
        COUNTROWS ( 'Actual Costs' ),
        FILTER (
            'Actual Costs',
            'Actual Costs'[Date] <= EARLIER ( 'Actual Costs'[Date] )
                && 'Actual Costs'[Forecastexists] = FALSE ()
        )
    )
RETURN
    IF (
        ISBLANK ( _result ),
        CALCULATE (
            SUM ( 'Actual Costs'[Actual] ),
            FILTER (
                'Actual Costs',
                'Actual Costs'[Date] <= EARLIER ( 'Actual Costs'[Date] )
                    && 'Actual Costs'[Forecastexists] = FALSE ()
            )
        ) / _rows,
        _result
    )

Please check the pbix as attached.

Regards,

Frank

[v-frfei-msft · Accepted solution]

Hi @steph_io ,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

Regards,
Frank