Forum Discussion
MEASURE DISTINCTCOUNT - LASTDATE/ MAXSCORE
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: =
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.
3 Replies
- amitchandak
Super User
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
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
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.
