cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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
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 6
Super User

Frequent Visitor

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?

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

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.

Super User

Frequent Visitor

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