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.

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
Icon for Community Support rankCommunity 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!