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

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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        That's perfect! Thanks!