Filter Report by Max Date

[aditidhooria]

Hello community,

I want to filter my report with the Max date in my Charts whenever user does not select any year. Do I need to write a query or DAX expression for that?

Can someone help with procedure in Power BI as I am a novice to this tool.

Thanks in advance !

[v-micsh-msft]

Hi aditidhooria,

I think this should be achieveable.

We need to write a condition to determine whether the year has been choosen. Fortunately there are DAX function available to use.

ISFILTERED () and HASONEFILTER().

We should use it under the value measure, to determine whether we would use the MAX date.

Formula should be in the format below:

ValueMeasure : = If (ISFILTERED('DateTable'[Year Column]),

                                 FirstDate('DateTable'[Date Column]) and LastDate ('DateTable'[Date Column]), 

                                 Max('DateTable'[Date Column]))

//Here are only list some date functions that we should take use in the measure, while the formula differs in different situations.

If you could provide some data sample, then we should be able to share a workable measure.

Regards

[aditidhooria]

Hi @v-micsh-msft,

I have a table named 'Satge2Calendar' in which I need to show data for maximum year and the column name is 'Fiscal Year'.

I need to find the maximum of year when the user has not selected any year i.e. the graph shows the data for the maximum year/ current year when there is no filter applied.

Following is the query implemented : 

MaxYear = IF(HASONEFILTER(Stage2Calender[Fiscal Year]), AND(FIRSTDATE('Stage2Calender'[Fiscal Year]),LASTDATE('Stage2Calender'[Fiscal Year])),MAX(Stage2Calender[Fiscal Year]))

The output is as follows : 

 Kindly help.

Thanks in advance !

[v-micsh-msft]

Hi aditidhooria,

Try to change the formula as below:

MaxYear : = IF(ISFILTERED(Stage2Calender[Fiscal Year]),

                        VALUES(Stage2Calender[Fiscal Year]),

                        MAX(Stage2Calender[Fiscal Year]))

 Use the FirstDate and LastDate function into the measure of calculating things other than the date itself.

Reply back if you need any further assistance.

Regards

[Greg_Deckler]

Hmm, I know that you can't use a measure in a filter for the value so would have to think of a creative way around that.

[aditidhooria]

Hi,

Could you please suggest as to how I can start with this ?

Thanks !