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

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.

     

4 Replies

  • 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

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

    • 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.

       

  • 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.