Forum Discussion

telesforo1969's avatar
telesforo1969
Helper V
1 year ago
Solved

Calculate Projection

I need your help with the following: I need to calculate the projection of a value as follows: the immediate previous value times the rate for the month to be calculated. Once the previous value is calculated, it must be repeated for the next period.
I have already calculated the rate using the measurement I present (TasaProyectada). I can't figure out how to use two measurements (SumaValorVista) and the other with the rate calculation (TasaProyectada). I have a Calendar Date Table.
I think I've already done the difficult part, and I don't know how to do the easy part.

SumaValorVista = Sum(TableX[Valor])

 

TasaProyectada =
VAR _NumberofYears = NoAnios[Valor de QtdMeses]
VAR _CurrentVisibleDate = MAX(Calendario[Date])
VAR _DatesTable =
    CALCULATETABLE(
        VALUES(Calendario[Date]),
        DATESINPERIOD(
            Calendario[Date],EOMONTH(_CurrentVisibleDate,-1),-_NumberofYears,YEAR),
            Calendario[Month Number] = MONTH(_CurrentVisibleDate)
    )
VAR _SumaTasaHistorica =
CALCULATE(
    [TasaHistorica],
    _DatesTable
)

RETURN
CALCULATE(
    AVERAGEX(VALUES(Calendario[Year]), [TasaHistorica]),
     _DatesTable
     )
Need Help
  • v-aatheeque's avatar
    v-aatheeque
    11 months ago

    Hi telesforo1969 ,

    Thanks for sharing the screenshots. as you need you can achieve the calculation of future values with the following DAX measures:  

    Inflation Rate  =
CALCULATE (
    PRODUCT ( Values[Inflation Rate] ),
    FILTER (
        ALL ( Calender ),
        Calender[Date] <= MAX ( Calender[Date] )
    )
)

    Future Values : 

    Future_Value =
MAX ( Values[Value] ) * [Cumulative_Inflation]


    Hope this helps !!



7 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    1 year ago

    telesforo1969 This looks a bit like a recursive calculation which can be problematic for DAX. For the next line (ago), would the next cell's calculation be the result of the calculation in the red box multiplied by the .002954 number? Are you able to post your date as text that can be copied?

     

    Sorry, having trouble following, can you post sample data as text and expected output?
    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

    • telesforo1969's avatar
      telesforo1969
      Helper V
      11 months ago

      I need help calculating future data. The formula is shown in the red box. I have a calendar table because I'm calculating my rate with my calendar table. I just need help calculating future values ​​using the rate.

       

       

       

      • v-aatheeque's avatar
        v-aatheeque
        Community Support
        11 months ago

        Hi telesforo1969 ,

        Thanks for sharing the screenshots. as you need you can achieve the calculation of future values with the following DAX measures:  

        Inflation Rate  =
CALCULATE (
    PRODUCT ( Values[Inflation Rate] ),
    FILTER (
        ALL ( Calender ),
        Calender[Date] <= MAX ( Calender[Date] )
    )
)

        Future Values : 

        Future_Value =
MAX ( Values[Value] ) * [Cumulative_Inflation]


        Hope this helps !!



  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

    Hi,

    I am not sure how much i can help but i'd like to try.  Share the download link of the PBI file.  If possible, please have column names in English.

  • v-aatheeque's avatar
    v-aatheeque
    Community Support
    11 months ago

    Hi telesforo1969 

    To help us reproduce and troubleshoot your scenario, we requested to share a small sample dataset/file. Could you please provide that when convenient?
    It will allow us to give you a more accurate and tailored solution.