Forum Discussion

Iamnvt's avatar
Iamnvt
Icon for Continued Contributor rankContinued Contributor
6 years ago
Solved

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

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

Need Help
  • AlB's avatar
    AlB
    6 years ago

    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  Datanaut

     

1 Reply

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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  Datanaut