Forum Discussion

aditidhooria's avatar
aditidhooria
Frequent Visitor
9 years ago

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 pr...
Interesting Links
Need Help
Show and Tell
Tips and Tricks
aditidhooria's avatar
aditidhooria
Frequent Visitor
9 years ago

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's avatar
v-micsh-msft
Microsoft Employee
9 years ago

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