first date **bleep** orders over **bleep** forecast

[Iamnvt]

hi,

I have a table:

Sequence	Forecast	Orders
1	10	4
2	21	50
3	32	24
4	11	21
5	55	11

I need to find the 1st sequence that cummulative of orders > cummulative forecast. In this case, it is 2.

I wrote the below measure, but it is not working:

1stseq = 
var 
cumforecast = CALCULATE(SUM('Table'[Forecast]), FILTER(ALL('Table'), 'Table'[Sequence] <= MAX('Table'[Sequence])))
var
cumorder = CALCULATE(SUM('Table'[Orders]), FILTER(ALL('Table'), 'Table'[Sequence] <= MAX('Table'[Sequence])))
var
firstseq = CALCULATE(FIRSTNONBLANK('Table'[Sequence], 0), FILTER(ALL('Table'), cumorder >= cumforecast))

return
firstseq

here is the PBI file:

https://1drv.ms/u/s!Aps8poidQa5zk7FOhEXm9PiaatnpCw?e=9JVp7E

thanks

[AlB · Accepted solution]

Hi @Iamnvt 

Try this measure in a card visual:

1stSeq V2 =
MINX (
    'Table';
    IF (
        CALCULATE (
            SUM ( 'Table'[Orders] ) - SUM ( 'Table'[Forecast] );
            ALL ( 'Table' );
            'Table'[Sequence] <= EARLIER ( 'Table'[Sequence] )
        ) > 0;
        'Table'[Sequence]
    )
)

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers