cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

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

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

Top Solution Authors
Top Kudoed Authors