cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Continued Contributor

## 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

1 ACCEPTED SOLUTION
Super User

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

Super User

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors