Forum Discussion

janislv's avatar
janislv
Helper I
4 years ago
Solved

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

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 dat...
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    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's avatar
amitchandak
Super User
4 years ago

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's avatar
janislv
Helper I
4 years ago

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.