Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Iamnvt
Continued Contributor
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
AlB
Super User
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  Datanaut

 

View solution in original post

1 REPLY 1
AlB
Super User
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  Datanaut

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.