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.

     

v-yanjiang-msft's avatar
v-yanjiang-msft
Community Support
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.