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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Select a column value based on filter

Hi all, 

I have data as follows:

 

Date                   MonthnYear

1/7/2020            July 2020

2/7/2020            July 2020

3/7/2020            July 2020

4/7/2020            July 2020

5/7/2020            July 2020

6/7/2020            July 2020

1/8/2020            Aug 2020

2/8/2020            Aug 2020

3/8/2020            Aug 2020

4/8/2020            Aug 2020

6/8/2020            Aug 2020

8/8/2020            Aug 2020

8/8/2020            Aug 2020

 

I have my max date values measures as follows:

1. Max Date = MAX('Date Table (Actuals)'[Date])

2. Max Date All = CALCULATE([Max Date], ALL('Date Table (Actuals)'))

What I need is, to bring my Monthnyear data to a card based on Max Date all.
 
So, since my max date all = 09/08/2020, I want to pull 'Aug 2020' in my card based on my 'max date all'.

Note: I tried using max all to my Monthnyear column but it returns Sep 2019 though it should have been Aug 2020. 

Would really appreciate the help
1 ACCEPTED SOLUTION
AllisonKennedy
Super User
Super User

@Anonymous

As @pranit828 has already mentioned, you can fix this by formatting your date measure. Just to explain why using max all to your Monthnyear column doesn't work is because this sorting them alphabetically rather than chronologically. Monthnyear is just a text data type, so will be sorted alphabetically.

Two ways to format:
1) if you want something specific in a card visual as requested, use the DAX FORMAT() function:

Max Month Year = FORMAT([Max Date All], "MMM YYYY")

NOTE: this method will convert your measure to a text data type. Shouldn't be a problem for measures, but for custom columns this means it will sort alphabetically as explained above.

2) Format the measure in the Model view using custom formatting https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-custom-format-strings

NOTE: This method just applies formatting on top of the existing measure without coverting the data type.


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

@Anonymous 

 

Your dax is almost ready .Use below change

Formatted Max Date=
VAR MaxDate = MAX('Date Table (Actuals)'[Date])

VAR MaxDateAll = CALCULATE([MaxDate], ALL('Date Table (Actuals)'))
RETURN

FORMAT(MaxDateAll,"MMM YYYY")
AllisonKennedy
Super User
Super User

@Anonymous

As @pranit828 has already mentioned, you can fix this by formatting your date measure. Just to explain why using max all to your Monthnyear column doesn't work is because this sorting them alphabetically rather than chronologically. Monthnyear is just a text data type, so will be sorted alphabetically.

Two ways to format:
1) if you want something specific in a card visual as requested, use the DAX FORMAT() function:

Max Month Year = FORMAT([Max Date All], "MMM YYYY")

NOTE: this method will convert your measure to a text data type. Shouldn't be a problem for measures, but for custom columns this means it will sort alphabetically as explained above.

2) Format the measure in the Model view using custom formatting https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-custom-format-strings

NOTE: This method just applies formatting on top of the existing measure without coverting the data type.


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

pranit828
Community Champion
Community Champion

Hi @Anonymous 

Try

FORMAT(MAX('Date Table (Actuals)'[Date]),"mmmm YYYY")





PBI_SuperUser_Rank@1x.png


Hope it resolves your issue? 
Did I answer your question? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!
Linkedin Profile
Anonymous
Not applicable

This works but I need All as well, to ignore row level filter in my visuals

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.