Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Custom forward projection

Hi All,

I have a table as shown below. FY and Qtr are derived from a [Dates] table. The values in Red are projected. Each projected value is based on the previous one (previous quarter). The black ones are observed values so 3377 is 3396 + X (X being a calculation based on a sum of parameters), 3368 is 3377 + X, and so on.

My [Dates] table has dates up to 2025 so I'd like to run that projection for all those dates.

 

FYQtrValue
FY20Q11186
FY20Q23087
FY20Q33310
FY20Q43780
FY21Q13717
FY21Q23396
FY21Q33377
FY21Q43368

 

Could someone help me with that or point me to an article?

Many thanks!

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    Hi Anonymous ,

    I created a sample measure for calculating the value of time-based variables: X, Y, Z...

    test parameters = QUARTER(MAX(Dates[Date]))*100

     

    Then create measures:

    Sum_Parameters = 
IF(
    COUNTROWS(Dates) = COUNTROWS( ALLSELECTED(Dates) ),
    SUMX(
        SUMMARIZE(
            'Dates',
            Dates[FY],
            Dates[Qtr],
            "_sum",
            [test parameters]
        ),
        [_sum]
    ),
    [test parameters]
)
    Measure = 
var Last_Date = MAXX(ALL('Table'),'Table'[Date])
var QuarterEnd = 
    CALCULATE(
        VALUES('Table'[Date]),
        ENDOFQUARTER('Table'[Date])
    )
var FactValue = SUM('Table'[Value])
var ProjectedValue = 
    CALCULATE(
        SUM('Table'[Value]),
        FILTER(
            ALL(Dates),
            Dates[Date].[Year] = YEAR(Last_Date)
            && Dates[Date].[QuarterNo] = QUARTER(Last_Date)
        )
    ) +
    CALCULATE(
        [Sum_Parameters],
        FILTER(
            ALL(Dates),
            Dates[Date] > Last_Date
            && Dates[Date] <= MAX(Dates[Date])
        )
    )
return 
    IF(
        MAX(Dates[Date]) > QuarterEnd,
        ProjectedValue,
        FactValue
    )

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , What you do is take max QTR Value and then take diff in Qtr and Multiple parameters with that

    3396 +X

    3396+ 2X

    Have a column 

    QTR in YYYYQ  format

    and qtr rank column

    Qtr Rank = RANKX(all('Date'),'Date'[QTR Rank],,ASC,Dense)

    Measure  =

    Var _max = Maxx(allselected(Table), Table[Qtr Rank]) // Have Year qtr in YYYYQQ format

    var _maxVal = maxx(filter(allselected(Table), Table[Qtr Rank] = _max), Table[Value])

    return

    _maxVal +(2* max(Table[Qtr Rank])  -_max)

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      amitchandak Thanks a lot for that quick response! I'm a bit confused though...

      I don't understand the "3396 + 2X" part and the DAX formula. Could you elaborate?

      Maybe I should provide clarity:

      First projected value is = previous value + X

      Second projected value is = first projected value + Y

      Third projected value is = second projected value + Z...

      X, Y and Z are calculations that are also date related.

       

      I have a Date table ready that is providing the Quarter value from a proper date

      So I guess I could work with time intelligence rather that RANK, right?

      • v-kkf-msft's avatar
        v-kkf-msft
        Community Support
        5 years ago

        Hi Anonymous ,

        I created a sample measure for calculating the value of time-based variables: X, Y, Z...

        test parameters = QUARTER(MAX(Dates[Date]))*100

         

        Then create measures:

        Sum_Parameters = 
IF(
    COUNTROWS(Dates) = COUNTROWS( ALLSELECTED(Dates) ),
    SUMX(
        SUMMARIZE(
            'Dates',
            Dates[FY],
            Dates[Qtr],
            "_sum",
            [test parameters]
        ),
        [_sum]
    ),
    [test parameters]
)
        Measure = 
var Last_Date = MAXX(ALL('Table'),'Table'[Date])
var QuarterEnd = 
    CALCULATE(
        VALUES('Table'[Date]),
        ENDOFQUARTER('Table'[Date])
    )
var FactValue = SUM('Table'[Value])
var ProjectedValue = 
    CALCULATE(
        SUM('Table'[Value]),
        FILTER(
            ALL(Dates),
            Dates[Date].[Year] = YEAR(Last_Date)
            && Dates[Date].[QuarterNo] = QUARTER(Last_Date)
        )
    ) +
    CALCULATE(
        [Sum_Parameters],
        FILTER(
            ALL(Dates),
            Dates[Date] > Last_Date
            && Dates[Date] <= MAX(Dates[Date])
        )
    )
return 
    IF(
        MAX(Dates[Date]) > QuarterEnd,
        ProjectedValue,
        FactValue
    )

        If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

        Best Regards,
        Winniz

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.