Forum Discussion
Evolution of value per id filtering by date
Hi,
I have been investigating on the forum but still have some issues.
I have a table like this:
| Id | Date | Value |
| 1 | 1/1/2020 | Prospect |
| 1 | 2/2/2020 | Lead |
| 1 | 5/5/2020 | Opportunity |
| 2 | 3/3/2020 | Prospect |
| 2 | 4/4/2020 | Lead |
| 2 | 7/7/2020 | Opportunity |
I need a measure that shows the last value by id filtering by month. For example, if I select march/2020 I need to have something like:
| Id | value |
| 1 | Lead |
| 2 | Prospect |
Then, I need to display a chart that shows the number of distinct id´s per value filtering by month
If I select 5/5/2020, I need to see something like:
Value Count of id
Prospect 0
Lead 1
Opportunity 1
This means, we count the number of id´s per value, depending on the last value of an id at a certain date
6 Replies
- lbendlin
Super User
Create a Dates table for your range, and connect it to your fact table
Dates = CALENDAR("2020-01-01","2020-07-31")(I also added Month and Year calculated columns)Then create a measure
Measure =var m = max(Dates[Month])var d = CALCULATE(max('Value by Month'[Date]),ALLEXCEPT('Value by Month','Value by Month'[Id]),month('Value by Month'[Date])<=m)var v= CALCULATE(max('Value by Month'[Value]),ALLEXCEPT('Value by Month','Value by Month'[Id]),'Value by Month'[Date]=d)return vand lastly put everything in a visual of your choice. I omitted the year check from the solution - you can add that yourself.
If you want a more elegant (but also costlier) measure you can use
Measure =var m = max(Dates[Month])return CALCULATE(LASTNONBLANKVALUE(Dates[Date],max('Value by Month'[Value])),month(Dates[Date])<=m)EDIT: after playing with this some more and reading some italian lyrics here's a better versionMeasure =var m = max(Dates[Month])var d = CALCULATE(max('Value by Month'[Date]),Dates[Month]<=m)return CALCULATE(max('Value by Month'[Value]),TREATAS({d},Dates[Date]))Thank you very much lbendlin , it works.
How can I display this measure on a chart like this one?:
I want to replicate your measure on this chart so that I can see the number of distinct Id´s based on their last value, filtering by Month.
- lbendlin
I don't understand your request. Please show the expected outcome.
- wdx223_Daniel
Community Champion
Thank you wdx223_Daniel !
It only works for the dates where it has NonBlank values.
How would you display on a chart like this one the number of distinct Id´s based on their last value, filtering by Month?
