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

Need Help

6 Replies

    • Vadim_Drevin's avatar
      Vadim_Drevin
      Frequent Visitor
      8 years ago

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

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