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

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'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