Select a column value based on filter

[Anonymous]

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

[Anonymous]

@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 · Accepted solution]

@Anonymous

As @Anonymous 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.

[Anonymous]

Hi @Anonymous 

Try

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

[Anonymous]

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