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, 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's avatar
Vadim_Drevin
Frequent Visitor
8 years ago

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 :manhappy: ):

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

 

 

 

 

 

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