MAXIFS for Dax - Forecast increase

[antalford88]

I'm trying to apply the same logic used in an excel doc within Power BI with little luck.

I have a set of forecasts listed out at forecast_name level. Each forecast is attributed to a date. I then have another document (parameter sheet) in which a user can amend certain elements which in turn either increase of decrease the existing forecast. 

The excel file contains a MAXIFS calculation per below:

=MAXIFS('CTR change sheet'!N:N,'CTR change sheet'!B:B,[@[forecast_name_high_level]],'CTR change sheet'!H:H,"<="&[@date])

Essentially this will increase or decrease the forecast_name level from the date listed on the parameter sheet, onwards.

My Power BI format include the existing list of forecasts by date and forecast_name. I've also uploaded the parameter sheet. I've creaed a calculated measure that will amend the forecast but as it stands it will inflate or deflate every date attributed to the forecast_name as opposed to the specific date + future dates.

Fcst Change = [Fcst] * (1 + [Expected Change in Fcst])

Both forecast sheet and parameter sheet are modelled to date dimensions and name dimension. 

Is there a way to wrap by [Fcst] * (1 + [Expected Change in Fcst]) in some logic that points to the date in the parameter sheet and MAXIFS that to the forecastsheet?

[v-yuta-msft · Accepted solution]

@antalford88 ,

Not very clear about excel formula. You may create a measure using dax like pattern below:

Result =
VAR _date =
    SELECTEDVALUE ( 'CTR change sheet'[forecast_name_high_level] )
RETURN
    CALCULATE (
        MAX ( 'CTR change sheet'[forecast_name_high_level] ),
        FILTER (
            'CTR change sheet',
            'CTR change sheet'[forecast_name_high_level] <= _date
        )
    )

You can also refer to the similar threads which may help you below:

https://community.powerbi.com/t5/Desktop/Dax-for-maxif/td-p/354825

https://community.powerbi.com/t5/Desktop/Translation-of-MAXIFS-excel-formula-into-DAX/td-p/75284

https://community.powerbi.com/t5/Desktop/DAX-Calculation-MAX-if-for-multiple-criteiria-Most-recent-d...

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.