Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |