cancel
Showing results for
Did you mean:
Highlighted
Iamnvt Member

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

Accepted Solutions Super User

## Re: first date **bleep** orders over **bleep** forecast

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

## Re: first date **bleep** orders over **bleep** forecast

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 Learn the answers to some of the questions asked during the Amanda Triple A event. #### October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.  #### Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI. Top Kudoed Authors
Users Online
Currently online: 140 members 1,410 guests
Recent signins:
• camronwilkins • JP8991 • QLD_Jay • soni27 • Alice_Drummond • AnandDV • CL7777 • AnandDV • guliyevfarhad • Elmonoq4 • donz • mohamed_mutter • xinxilas • Karung001 