Need help with creating a calculated measure with filter in the data table

[WencyREN]

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

[Jihwan_Kim · Accepted 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]
    )
)

[tamerj1]

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] )
    )

[johnt75]

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