How to calculate future months

[Vadim_Drevin]

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 last month of the MeasureX starting from the last_month+1 and ending EOY. So that my data grid will be like this:

Year, Month, MeasureX, MeasureFuture

2018, 06, 100,

2018, 07, 120,

2018, 08, 140, 

2018, 09, 200,

2018, 10,   , 200

2018, 11,   , 200

2018, 12,   , 200

Please advise.

[v-xjiin-msft]

Hi @Vadim_Drevin,

In your scenario, is this Dim_Date table a normal calendar table? Did you create any relationships between dim_Date and other fact tables? What's the expression of MeasureX?

Please share us some sample data of your original table which we can copy and paste directly and its corresponding expected result. So that we can make some tests and provide more accurate solutions.

Thanks,
Xi Jin.

[Vadim_Drevin]

Hi @v-xjiin-msft,

Sure, let me provide the real case.

dim_Date = CALENDAR( "1/1/2016", "12/31/2019")

dim_Date is related to Fact tables.

MeasureX=

MeasureX = CALCULATE(SUMX(tbl_Workload, [Workload_x_Rate]*[Probability])*100/1000,
  FILTER(dim_Date, dim_Date[Date]>[MaxDateOfCostOfRevenue])
)

where Workload_x_Rate is another measure (multiplication tbl_Workload[workload] to a column from another table)...

My task it to get the output like this (numbers in red rectangle are drawn in Paint  😞

where 26 is the value of Measure X for the last calculated month (2018-Oct).

[v-xjiin-msft]

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]

@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..

[ImkeF]

This seems to be similar to what you're after: https://community.powerbi.com/t5/Desktop/lastnonblank-in-measure/td-p/310734

[Vadim_Drevin]

@ImkeF, thanks. Tried to compare it with my issue, but couldn't. Can you please suggest how to solve my problem with those functions.