cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aditidhooria Frequent Visitor
Frequent Visitor

Filter Report by Max Date

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 !

5 REPLIES 5
Super User
Super User

Re: Filter Report by Max Date

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


aditidhooria Frequent Visitor
Frequent Visitor

Re: Filter Report by Max Date

Hi,

 

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

 

Thanks !

v-micsh-msft New Contributor
New Contributor

Re: Filter Report by Max Date

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

Highlighted
aditidhooria Frequent Visitor
Frequent Visitor

Re: Filter Report by Max Date

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 : 

 

Capture.PNG

 Kindly help.

 

Thanks in advance !

 

v-micsh-msft New Contributor
New Contributor

Re: Filter Report by Max Date

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