DAX Function Help

[MaheshDadhich]

Hello,

My requirement is to calculate the MAX  Extractdate(Column Name) for each month in dataset/Table  after applying the user Filter in Power BI Desktop. So Once the dataset is filtered after applying Report parameter then DAX function should calculate the MAX date for each month in dataser which will be used in report.

I have tried ALLEXCEPT function But It calculate the MAX date before filter the data(By User parameter) in dataset which should be after filter the data.

For Example :  Below is the Dataset

Plant	Component	Material	Month	ExtractDate	Forecast
P1	C1	M1	201601	5-Jan-16	10
P1	C1	M2	201601	10-Jan-16	4
P1	C1	M3	201601	15-Jan-16	15
P2	C1	M4	201601	20-Jan-16	11
P2	C1	M5	201601	25-Jan-16	12

If User select Plant = P1 and Component = C1 then Max(ExtractDate) should be 15-Jan-16 But in my case It is showing 25-Jan-16.

How to calculate Max date dynamically based on user filter.

Thanks in Advance to help me

Regards,

Mahesh 

[v-qiuyu-msft]

Hi @MaheshDadhich,

According to your description, you have added two query parameters to filter both [Plant] and [Component] fields. You want to return maximum date of the column [ExtractDate] when check different parameter values, right?

In your scenario, you can create a measure with MAX() function to return the maximum date of the date filed. See:

I have attached the test .PBIX file, you can open it in the latest Power BI Desktop to have a look.

If you have any question, please feel free to ask.

Best Regards,
Qiuyun Yu

[MaheshDadhich]

Hi Qiuyun Yu, Actually my dataset is like that 

Plant	Component	Material	Forecast_Period	Last_Changed_Date	Forecast	MAX Date
P1	C1	M1	201605	5/10/2016	10	5/12/2016
P1	C1	M2	201605	5/12/2016	12	5/12/2016
P1	C1	M2	201606	6/25/2016	35	6/27/2016
P1	C1	M8	201606	6/27/2016	70	6/27/2016
P2	C1	M3	201605	5/20/2016	20	5/25/2016
P2	C1	M4	201605	5/25/2016	50	5/25/2016
P2	C1	M4	201606	6/23/2016	100	6/23/2016

If I select Plant = P1 , Component = C1 and Material = ALL the result should be like that with Calculated field MAX Date

Plant	Component	Material	Forecast_Period	Last_Changed_Date	Forecast	MAX Date
P1	C1	M1	201605	5/10/2016	10	5/12/2016
P1	C1	M2	201605	5/12/2016	12	5/12/2016
P1	C1	M2	201606	6/25/2016	35	6/27/2016
P1	C1	M8	201606	6/27/2016	70	6/27/2016

If I use MAX(Last_Changed_Date)  then It will give max date for entire date which will be 6/27/2016 But I need max date for each Forecast_Period as above.

[v-qiuyu-msft · Accepted solution]

Hi @MaheshDadhich,

In your scenario, you can specify the expression for MAX Date like below:

MAX Date = CALCULATE(MAX('Table1'[Last_Changed_Date]),
             FILTER(ALLSELECTED(Table1),'Table1'[Forecast_Period]=EARLIER(Table1[Forecast_Period])
  && 'Table1'[Plant]=EARLIER(Table1[Plant])))

Best Regards,
Qiuyun Yu