Create measure to calculate the max of another measure within Date Range

[doubleclick]

Hello all,

I have a measure that calulcates the number of leavers depending on the calendar table date.

Net Finishers:=CALCULATE (
    DISTINCTCOUNT ( 'FACT_EDE'[Worker] ),
    FILTER (
        'FACT_EDE',
        'FACT_EDE'[End Date] <= MAX ( 'Calendar'[Date] )
            && 'FACT_EDE'[End Date] >= MIN ( 'Calendar'[Date] )
    )
)

I have another measure to show me todays date :

Today:=IF(MIN('Calendar'[Date]) <= TODAY() && MAX('Calendar'[Date]) >= TODAY(), 60)

That 60 at the end, i want that to be the max of the finishers within the calendar date range. This is so i have have the number of leavers going forward into the future and a signle bar that will go to the top of the graph by the maximum number of leavers in the graph. 

Can anyone figure this out?

[wdx223_Daniel · Accepted solution]

@doubleclick  say you have Year/Month series on the x axis, then create a measure like this

MaxFinishersToDate=IF(MIN('Calendar'[Date]) <= TODAY() && MAX('Calendar'[Date]) >= TODAY(), MAXX(FILTER(ALL('Calendar'[Year/Month]),[Net Finishers]))

[doubleclick]

It worked with some minor adjustments!! Thank you so much you legend!!! 

MaxFinishersToDate:=IF(MIN('Calendar'[Date]) <= TODAY() && MAX('Calendar'[Date]) >= TODAY(), MAXX(FILTER(ALL('Calendar'[Date]),[Net Finishers]),[Net Finishers]))

[edhans]

You are going to have to provide data and expected results @doubleclick . You cannot have one measure determine the MAX of another measure, because a measure always only returns one value. It may be calculated multiple times within a visual, but the value of a measure is what you drop into a card. The reason it shows other values in a chart, table, etc. is the filter context changes it (days, customers, vendors, etc.)

So to do what you want, you'd need a new measure that calcaulted the max of a table of data. But we'd need data (per links below) and sample output.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum