- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Create a Dates table for your range, and connect it to your fact table
Then create a measure
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I don't understand your request. Please show the expected outcome.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
07-30-2024 12:36 PM | |||
09-03-2024 07:18 AM | |||
06-21-2024 12:24 AM | |||
07-21-2024 05:50 AM | |||
04-30-2024 04:21 PM |
User | Count |
---|---|
14 | |
14 | |
11 | |
11 | |
8 |