Get last value in date selection for a string

[Mype_powerbi]

Hello,

I'm spending much time on doing something I've already done with numbers and now I 'm struggling to do it with text values. 

I have a table (Records_Africa) with two columns, [AGILITY SCORE] and [Month]. 

Agility score visual should display [AGILITY SCORE] value from July 2016 because user sliced it.

[AGILITY SCORE] contains text values such as "WARNING", "EXCELLENT", "ALERT".

In my report, a date slicer uses [Month] column and this slicer is filtered by the user. 

There is also a Card visual that displays Agility Score.

I want this card visual to display only LATEST value of AGILITY SCORE, within the Month slicer selection made by the user.

How can I do that please ?

PS : With numbers, formula works, it is :

CALCULATE(SUM('Records_Africa'[Monthly Downloads]);LASTDATE('Records_Africa'[Month]))

Please just teach me how to do it with a field that is not numbers !

[Greg_Deckler]

Just try MAX instead of LASTDATE

[Mype_powerbi]

@Greg_Deckler ,

False.

The field is not numbers but text. 

I think we should replace CALCULATE AVERAGE by smth else but I don't know how.

[v-lili6-msft]

hi, @Mype_powerbi 

You need to add a map calculated column for [Month] for calculation or add a dim date table then create the relationship with basic data by month, and then use it for calculation.

Best Regards,

Lin

[Mype_powerbi]

Hello @v-lili6-msft ,

Thanks for answering me.

Ok, I've added another table with Date dimension called Time.

What do I have to do now ?

My Time table is generated by a DAX formula.

[tex628 · Accepted solution]

Replace the AVERAGE() with a SELECTEDVALUE()?

Measure = 
VAR maxDate = MAX('Records_Africa'[Month])
Return
CALCULATE(SELECTEDVALUE('Records_Africa'[Agility Score]);
'Records_Africa'[Month] = maxDate
)

But you will have an issue if there are more than 1 agility score on the same date. 

[Mype_powerbi]

thank you for helping me @tex628 .

Unfortunatly this doesn't work for me 😞

There is only one Agility Score by Month, so it should be fine.there is only one AGILITY score for each month.

The visual displays "Blank" with your measure. Maybe because several months are selected in the Month Slicer ?

I put your measure in the visual, it always displays blank.

[tex628]

Can you take a picture of the 

'Records_Africa'[Month]

column? 

[Mype_powerbi]

@tex628 

please find screenshot here

[tex628]

Use 

Measure = 
VAR maxDate = MAX('Records_Africa'[Month])
Return
CALCULATE(DISTINCTCOUNT('Records_Africa'[Agility Score]);
'Records_Africa'[Month] = maxDate
)

and show me the result. Need to figure out what the issue is 🙂

[Mype_powerbi]

Hey @tex628 ,

Thanks for caring about my problem.

Please find here the image : Things are not better because I want to have the value of AGILITY SCORE for a given month, and it is a text value 😞

[tex628]

Alright, from what i can tell there appears to be more than 1 agility score for a single month.

To make sure do the following: 
Create a new table. 

Add the following columns:
* Month
* Agility score
Filter on ONE month. 

Take a picture of the table and the filter! 🙂

[Mype_powerbi]

@tex628 

Ok it works !!!

Now that I really have 1 Agility Score Value by Month, I put your measure :

Measure =

VAR maxDate = MAX('Records_Africa'[Month])

Return

CALCULATE(SELECTEDVALUE('Records_Africa'[Agility Score]);

'Records_Africa'[Month] = maxDate

)

And It works 🙂

Thank you for helping me get my problem resolved !!

Best regards

[tex628]

Im glad it works! 🙂

[Mype_powerbi]

Hello @tex628 

You are right.

There is more than 1 Agility Score per month.

I need to fix that and then I will use your formula.

Which one is supposed to work ?

Many thanks, you help me so much !

Once it works I will tag your answer as Accepted Solution.

Augustin

[v-lili6-msft]

hi, @Mype_powerbi 

Create the relationship with [Month] column, then use date field from this dim table for report.

If possible, please share your sample pbix file and your expected output and I could test on it.

Best Regards,

Lin

[Mype_powerbi]

Hi @v-lili6-msft ,

Please find here the link to my pbix file "Essec_Training_File_test".

https://drive.google.com/file/d/185cQPatOToBxKsnr-J_bFclWHp-UBXRb/view?usp=sharing

Visual AGILITY SCORE must display values like here :

there is 1 Agility Score for each month. ie for June 2016, it should be ALERT, and for March 2016 it should be WARNING;.

Best regards