Forum Discussion

Vadim_Drevin's avatar
Vadim_Drevin
Frequent Visitor
8 years ago

How to calculate future months

I have dim table dim_Date and some Fact table. In my Fact table max value for 'date' column is let's say Sep-2018 (2018-09). I need to create a separate measure MeasureFuture which will equal to the ...
Need Help
v-xjiin-msft's avatar
v-xjiin-msft
Icon for Solution Sage rankSolution Sage
8 years ago

Hi Vadim_Drevin,

 

In your scenario, to achieve your requirement, the most important point is to get the last value. So check following measure, hope it works for you:

 

=
VAR LastValue =
    CALCULATE (
        [MeasureX],
        'dim_date'[month] = MONTH ( MAX ( tbl_Workload[Date] ) )
            && 'dim_date'[Year] = YEAR ( MAX ( tbl_Workload[Date] ) )
    )
RETURN
    IF ( ISBLANK ( [MeasureX] ), LastValue )

By the way, since I don't know your actual situation. Above expression is just my assumption. If you want more accurate suggestions, your pbix file is necessary. 

 

Thanks,
Xi Jin.

Vadim_Drevin's avatar
Vadim_Drevin
Frequent Visitor
8 years ago

v-xjiin-msft, unfortanaitly this expression doesn't work. The following error appears: "A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed.".

Tried to use another filter expression in Calculate function like 

... FILTER(dim_Date, dim_Date[Date]=MAX(tbl_Workload[Date])) ...

, but in this case the return equal to [MeasureX] for every month, but not for last month..