Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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
Need Help
  • AllisonKennedy's avatar
    AllisonKennedy
    5 years ago
    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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous 

    Try

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

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

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    5 years ago
    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's avatar
    Anonymous
    Not applicable
    5 years ago

    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")