Measure to calculate future quarter backlog

[DarrenLau]

Hi,

Looking for some help in creating a measure to showfuture quarter backlog on the same page that is filtered to current quarter.

Scenario: Report page is filtered (sliced?) to show 2019 Q4 data. I want to also show 2020 Q1 backlog on the same page.

Additional info: My dataset has a date table set up with weeks, quarters, etc in numeric (1,2,3 ...etc)  as well as labels (eg Q1, Week 1).

[v-lid-msft · Accepted solution]

Hi @DarrenLau ,

Sorry for late reply, we can create a measure using following formula to meet your requirement.

NextQuarterValue =
VAR m =
    MONTH ( MAX ( 'Table'[Date] ) )
VAR CurrentQuarter =
    SWITCH (
        TRUE (),
        m IN { 1, 2, 3 }, 1,
        m IN { 4, 5, 6 }, 2,
        m IN { 7, 8, 9 }, 3,
        4
    )
VAR maxday =
    DATE ( YEAR ( MAX ( 'Table'[Date] ) ), CurrentQuarter * 3, SWITCH ( CurrentQuarter, 1, 31, 2, 30, 3, 30, 4, 31 ) )
VAR firstday = maxday + 1
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            [Date] >= firstday
                && [Date]
                    <= DATE ( YEAR ( firstday ), MONTH ( firstday ) + 3, 1 ) - 1
        )
    )

If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

Best regards,

[DarrenLau]

Thank you for the detailed measure. I will apply it using the fields in my model and test it out.

[DarrenLau]

The measure works with a slight modification - the backlog table has an active relationship based on Order Entry Date, and a secondary relationship on Estimated Ship Date.

I substituted 

SUM ( 'Table'[Value] )

with the backlog measure using the Estimated Ship Date relationship

Backlog = calculate(sum('Backlog'[BacklogUSD]), USERELATIONSHIP( Backlog[EstShipDate], 'Calendar'[Date]))

Thanks for the help, much appreciated.