topic Re: Evolution of value per id filtering by date in DAX Commands and Tips

Evolution of value per id filtering by date

Avantgarde — Tue, 16 Feb 2021 14:43:28 GMT

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

Re: Evolution of value per id filtering by date

lbendlin — Tue, 16 Feb 2021 02:16:38 GMT

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

Re: Evolution of value per id filtering by date

wdx223_Daniel — Tue, 16 Feb 2021 04:03:01 GMT

Re: Evolution of value per id filtering by date

Avantgarde — Tue, 16 Feb 2021 11:24:48 GMT

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.

Re: Evolution of value per id filtering by date

Avantgarde — Tue, 16 Feb 2021 11:24:17 GMT

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?

Re: Evolution of value per id filtering by date

lbendlin — Tue, 16 Feb 2021 11:26:59 GMT

I don't understand your request. Please show the expected outcome.

Re: Evolution of value per id filtering by date

Avantgarde — Tue, 16 Feb 2021 12:03:04 GMT

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