DAX calculation with time period selection

[Anonymous]

Hi, I am new to use DAX, and I want to create two calculation to calculate metrics,

first is to calculation the acculative forecast value based start and end date selected,

second is when date is prior to present then select actual, if later than now then select forecast, and sum the values, and should also consider the date slicer

for example:

start_month_silcer=202206 end_month_silcer=2022010, and current date is 202208,

so the first value(accumulative value of forecast) is 3, 10, 12, 17, 20, which is from 202206 to 202210

the second value is (7+3+2 + 5+3）

Is there a solution to fulfill these two requirements? Thank you!

[v-cgao-msft · Accepted solution]

Hi @Anonymous ,

My test process is as follows:

1. First create a calendar table like this, and create the relationship:

2. Then create these three measures:

Value of forecast = 
VAR _min =
    MIN ( 'Calendar'[Year_Month] )
VAR _max =
    MAX ( 'Calendar'[Year_Month] )
VAR _result =
    CALCULATE (
        SUM ( 'Table'[Forecast] ),
        FILTER (
            'Calendar',
            'Calendar'[Year_Month] >= _min
                && 'Calendar'[Year_Month] <= _max
        ) 
    )
RETURN
    _result

Accumulative value of forecast = 
SUMX (
    FILTER (
        ALLSELECTED ( 'Calendar' ),
        'Calendar'[Year_Month] <= MAX ( 'Calendar'[Year_Month] )
    ),
    [Value of forecast]
)

Actual_Forecast = 
VAR _min =
    MIN ( 'Calendar'[Year_Month] )
VAR _max =
    MAX ( 'Calendar'[Year_Month] )
VAR _today =
    TODAY ()
VAR _yearmonth =
    CALCULATE ( MAX ( 'Calendar'[Year_Month] ), 'Calendar'[Date] = _today )
VAR _actual_total =
    CALCULATE (
        SUM ( 'Table'[Actual] ),
        FILTER (
            'Calendar',
            'Calendar'[Year_Month] >= _min
                && 'Calendar'[Year_Month] <= _yearmonth
        )
    )
VAR _forest_total =
    CALCULATE (
        SUM ( 'Table'[Forecast] ),
        FILTER (
            'Calendar',
            'Calendar'[Year_Month] > _yearmonth
                && 'Calendar'[Year_Month] <= _max
        )
    )
VAR _result = _actual_total + _forest_total
RETURN
    _result

3. Result:

4. The PBIX file is attached for reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data