Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

finding first/minimum value in a measure

I have a situation where i have a calculated measure which begins summing on a date chosen by a slicer.  This is then comparing this sum against a column of data.  I then need to be able to tell when the measure exceeds the value of the column. It looks like this, where Revenue is the column of values,  SumProf-AfterProjEnd is the first measure, and BreakEvenMore is the first date where 

SumProf-AfterProjEnd is greater than Revenue.  

The code looks like this for the first measure column

 

SumProf-AfterProjEnd = 
VAR ProjEndDate = MAX(ProjDateSelector[Project End Date])  --ProjDateSelector[Project End Date] --- is the value from the slicer
VAR Result = 
            'Profit Margin Assumption'[Profit Margin Assumption Value] * 
            CALCULATE(SUM(GA_Data[Revenue]),
            FILTER(ALL('Date'),
            'Date'[Date] > ProjEndDate  && 'Date'[Date] <= MAX('Date'[Date])
            )
            )
RETURN
Result

 

The code looks like this for the 3rd column with the dates

 

 

BreakEvenMore = 
    VAR ProjEndDate = MAX(ProjDateSelector[Project End Date])
    VAR BreakEvenDate =     
            IF([SumProf-AfterProjEnd] < [ClientProjectSpend], BLANK(),
            MAX('Date'[Date]))
RETURN
    BreakEvenDate

 

 

Help?

 

  • v-easonf-msft's avatar
    v-easonf-msft
    5 years ago

    Hi, Anonymous 

    Try to add a new measure  as below:

    result =
VAR tab =
    SUMMARIZE (
        'Date',
        'Date'[Date],
        "SumProf-AfterProjEnd", [SumProf-AfterProjEnd],
        "BreakEvenMore", [BreakEvenMore]
    )
RETURN
    MINX ( tab, [BreakEvenMore] )

    or

    Result = MINX(all('Date'),[BreakEvenMore])

     

    If it doens't meet your requirement,  please share  a sample file for further research.

     

    Best Regards,
    Community Support Team _ Eason

     

1 Reply

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    5 years ago

    Hi, Anonymous 

    Try to add a new measure  as below:

    result =
VAR tab =
    SUMMARIZE (
        'Date',
        'Date'[Date],
        "SumProf-AfterProjEnd", [SumProf-AfterProjEnd],
        "BreakEvenMore", [BreakEvenMore]
    )
RETURN
    MINX ( tab, [BreakEvenMore] )

    or

    Result = MINX(all('Date'),[BreakEvenMore])

     

    If it doens't meet your requirement,  please share  a sample file for further research.

     

    Best Regards,
    Community Support Team _ Eason