Evolution of value per id filtering by date

[Avantgarde]

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

[wdx223_Daniel]

[Avantgarde]

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?

[lbendlin]

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 v

and 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 version

Measure =

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

[Avantgarde]

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.

[Avantgarde]

Hi @lbendlin 

Assuming the first data table of the post.

If I select 5/5/2020 for instance, 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