Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aditidhooria
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
v-micsh-msft
Employee
Employee

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

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 !

 

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
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi,

 

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

 

Thanks !

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.