How to get the earliest date when table is a variable?

[janislv]

I'm trying to get a single date value that would be used as a reference for further calculations. So far I have the correct result for first date when [value_Y] >= 8, but I need this on all other dates as well. This looks so simple that I have a feeling about missing some fundamental rule.

Measure_Z =
VAR table_x =
    FILTER (
        ADDCOLUMNS ( VALUES ( 'Calendar'[Date].[Date] ), "value_Y", [Measure_Y] ),
        [value_Y] >= 8
    )
RETURN
    MINX ( table_x, 'Calendar'[Date].[Date] )

[v-yanjiang-msft · Accepted solution]

Hi @janislv ,

According to your description, I create a sample. By your Measure_Z formula, get the result like yours', I'm not sure your expected result is like MeasureZ1 or MeasureZ2. 

Here's the foumula for the two situation.

MeasureZ1 =
MINX (
    FILTER ( ALL ( 'Calendar' ), 'Calendar'[Measure_Y] >= 8 ),
    'Calendar'[Date]
)

MeasureZ2 = 
VAR _MIN =
    MINX (
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Measure_Y] >= 8 ),
        'Calendar'[Date]
    )
RETURN
    IF ( MAX ( 'Calendar'[Date] ) = _MIN, _MIN, BLANK () )

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.

[amitchandak]

@janislv , try like

Measure_Z =
VAR table_x =
FILTER (
ADDCOLUMNS ( Summarize ( 'Calendar'[Date] ), "value_Y", [Measure_Y] ),
[value_Y] >= 8
)
RETURN
MINX ( table_x, 'table_x'[Date] )

[janislv]

This is the result I got. Measure_Y is a rolling average of Measure_A. So in this case condition [value_Y] >= 8 refers to Measure_A.

After adding VALUES ( 'Calendar'[Date].[Date] ), the condition [value_Y] >= 8 refers to Measure_Y, as it should be.

[janislv]

Nope, this looks the same as my first guess (before adding VALUES()):

Measure_Z =
VAR table_x =
    FILTER (
        ADDCOLUMNS ( 'Calendar', "value_Y", [Measure_Y] ),
        [value_Y] >= 8
    )
RETURN
    MINX ( table_x, 'Calendar'[Date] )