MEASURE DISTINCTCOUNT - LASTDATE/ MAXSCORE

[julrua]

Dear all, 

Objective: I want to develop a measure to count distincly the number of my project based on the last date. I have the following column refering to a date: 

• Month Running: Jan, Feb, etc. Format: Text 
• YTD: YTD01, YTD02, etc. Format: Text 

I have transformed Month Running into date and extract Month.

This steps was to use the following measure: =

CALCULATE(DISTINCTCOUNT('Business management (customer HUB)'[Project Definition 2]),LASTDATE('Evolution analyses'[Month Running ]))

I have tried several options but I still get an error message that my column is not in date format. 

Would it be possible to score Month Running from 1 to 12 and count based on the max number? 

Thank you in advance for you help. 

Kind regards, 

[V-pazhen-msft · Accepted solution]

@julrua 

To calculate the max month you must have a month number column instead month name column, because you cannot compare Texts. With month number column, you can try the following formula:

For example:
column = CALCULATE(DISTINCTCOUNT('Business management (customer HUB)'[Project Definition 2]),filter('Business management (customer HUB)', [month number] = maxx(all(['Business management (customer HUB)'),[month number])))

Instead of share the image, if you can just provide a sample table with a date column that allow me to copy paste. I could make a test and come out with a more definite solution.

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@julrua , not very clear. Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

check if these can help closingbalancemonth, closingbalanceyear : https://youtu.be/yPQ9UV37LOU

[julrua]

@amitchandak thank you for you quick reply. 

I have the following information into Evolution analyses table. 

I need to count distincly the number of project definition for the last period.

For example, I will need to calculate the distinct number of project definition for December because it is the last period for 2020. 

Is that more clear? 

Thank you