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.
Dear all, I need help with creating a calculated measure, please instruct on how to create such a measure to meet the requirement and sample data below, Thank you in advance!
Background: each product has 3 development stages, and each stage has an approved date (for product C in this case, it doesn't have stage 3 / stage 3 approved date record line as it's still under development). I want to create a measure that apply below logic:
(1) Select the product that has completed the 3 stages of development (in this case - select products A, B)
(2) Following the above, calculate the days' difference between each product's Stage 3 approved date and Stage 1 approved date, to see if it takes more than 2 months from stage 1 to stage 3
(3) Following above, sum up the development cost for those products taking more than 2 months from Stage 1 - Stage 3 and display the sum up cost in visuals
Product | Stage | Approved stage date | Development cost in value |
A | 1 | 01-Jul-21 | 100 |
A | 2 | 01-Nov-21 | 200 |
A | 3 | 10-Dec-21 | 400 |
B | 1 | 02-Aug-21 | 300 |
B | 2 | 04-Sep-21 | 250 |
B | 3 | 10-Dec-21 | 200 |
C | 1 | 01-Feb-22 | 400 |
C | 2 | 01-Mar-22 | 500 |
Solved! Go to Solution.
Hi,
I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.
expected outcome measure: =
CALCULATE (
SUM ( Data[Development cost in value] ),
TREATAS (
SUMMARIZE (
FILTER (
ADDCOLUMNS (
GROUPBY (
FILTER (
ADDCOLUMNS (
Data,
"@condition", COUNTROWS ( FILTER ( Data, Data[Product] = EARLIER ( Data[Product] ) ) )
),
[@condition] = 3
),
Data[Product],
"@mindate", MINX ( CURRENTGROUP (), Data[Approved stage date] ),
"@maxdate", MAXX ( CURRENTGROUP (), Data[Approved stage date] )
),
"@diff", INT ( [@maxdate] - [@mindate] )
),
[@diff] >= 60
),
Data[Product]
),
Data[Product]
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.
expected outcome measure: =
CALCULATE (
SUM ( Data[Development cost in value] ),
TREATAS (
SUMMARIZE (
FILTER (
ADDCOLUMNS (
GROUPBY (
FILTER (
ADDCOLUMNS (
Data,
"@condition", COUNTROWS ( FILTER ( Data, Data[Product] = EARLIER ( Data[Product] ) ) )
),
[@condition] = 3
),
Data[Product],
"@mindate", MINX ( CURRENTGROUP (), Data[Approved stage date] ),
"@maxdate", MAXX ( CURRENTGROUP (), Data[Approved stage date] )
),
"@diff", INT ( [@maxdate] - [@mindate] )
),
[@diff] >= 60
),
Data[Product]
),
Data[Product]
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi @WencyREN
you may try
Months Approved Products Development Cost =
VAR FirstDate =
MIN ( Table[Approved stage date] )
VAR LastDate =
MAX ( Table[Approved stage date] )
VAR Duration = LastDate - FirstDate
VAR NumberOfSatages =
COUNTROWS ( Table )
RETURN
IF (
NumberOfSatages = 3
&& Duration > 60,
SUM ( Table[Development cost in value] )
)
Assuming that products must have passed stage 2 to be able to pass stage 3, and assuming that each product has only 1 entry per stage, you could do something like
Total Cost =
VAR passedStage3 =
ADDCOLUMNS (
CALCULATETABLE ( VALUES ( 'Table'[Product] ), 'Table'[Stage] = 3 ),
"stage 1 date",
VAR currentProduct =
CALCULATE ( SELECTEDVALUE ( 'Table'[Product] ) )
RETURN
LOOKUPVALUE (
'Table'[Approved stage date],
'Table'[Product], currentProduct,
'Table'[Stage], 1
),
"stage 3 date",
VAR currentProduct =
CALCULATE ( SELECTEDVALUE ( 'Table'[Product] ) )
RETURN
LOOKUPVALUE (
'Table'[Approved stage date],
'Table'[Product], currentProduct,
'Table'[Stage], 3
)
)
RETURN
CALCULATE (
SUM ( 'Table'[Development cost in value] ),
FILTER ( passedStage3, [stage 3 date] - [stage 1 date] > 60 )
)
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 |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |