Forum Discussion
How to Graph using a Time Variable using a Measure Calculated with Dates Between
Hi Shelley ,
Thank you for reaching out to this forum.
I reviewed your post and now I have some questions for you.
1. What's your expression of [Sales Measure]?
2.You main table has two dates called [Synergy Start Date] and [Synergy End Date]. I am confused that how do you connect which date column to the date column of the date table?
3.From my preliminary judgment, it is possible that the values displayed in your donut table are the same because the date columns of the date table are not grouped and filtered in the formula.
In order to better solve the problem, please provide me with more detailed information and I am happy to help you.
Best Regards,
Yang Liu
Hello Anonymous
Thanks for reviewing my ask for help. Regarding your questions:
1. Allow me to provide some more background. We have another team here who's developed a large and complex sales and marketing data model. In order for me to fulfill the request I have, I must use their model. It has all the tables and relationships set up already, including the relationships to the date table. This model also includes a measure to calculate sales, using all the tables and relationships necessary in this model. So, in other words, there's not a column in a single table that I can sum, using filters, to calculate the sales value required. I must use the [Sales Measure] they have in the model. I do not have visibility into the DAX for this measure and if I did, I wouldn't understand it nor be able to explain it here.
2. We're selecting customers by their first purchase of a particular product. This is the [Synergy Start Date]. We then want to track ALL sales to each of these customers for 24 months. The end date of tracking is the [Synergy End Date]. So every customer ID could have different start and end dates, depending on their first purchase of a particular product. (I think I have all this created, so don't need help with this part.) It looks like this:
Customer ID Synergy Start Date Synergy End Date
99108672 6/4/24 6/3/26
8564909 5/23/24 5/22/26
15239356 5/1/24 4/30/26
991352113 5/1/24 4/30/26
15874218 4/1/24 3/31/26
This table is not connected into the date table because to your point, we have start and end dates only. (I've tried creating a table with all dates each customer ID is supposed to be tracked, but when I try to connect to the model, I receive ambiguous relationship errors or messages that the customer is already filtered another way.)
Nevertheless, I figured out how to calculate the Sales for each customer over time, by using this measure:
Synergy =
CALCULATE( [Sales Measure],
DATESBETWEEN('Date'[Date],
MIN('Customer Synergy IDs'[Synergy Start Date]),
MAX('Customer Synergy IDs'[Synergy End Date])))
This works for each customer, but when I use a table visual, the grand total on the table is incorrect because we need to iterate over each customer first and then calculate the total. So, to correct this issue, I added this measure and use this in my table visual (there's probably a more efficient way to do this, but I didn't figure it out yet).
Synergy SUMX =
SUMX(
FILTER('Customer Synergy IDs', 'Customer Synergy IDs'[Customer ID] <> BLANK()),
[Synergy])
3. The donut visuals by product work because I am not splitting it down by any time element. It's the bar or line chart -- when I want to look at the [Synergy] over time, that I couldn't get to work. Although I did come up with something as I'll share below, it's still not perfect because when I filter on product or business segment, etc. the visual doesn't get filtered because I used a calculated table and column.
Here's what I did.
I created a calculated date table, based on all the customer ID synergy start and end dates:
Customer Synergy Calendar =
CALENDAR(MIN('Customer Synergy IDs'[Synergy Start Date]), MAX('Customer Synergy IDs'[Synergy End Date]))
I connected this via a bi-directional relationship to from the date column here to the date column in the model's calendar table.
Then I created a column in the Customer Synergy Calendar that calculates the Synergy for each date.
Customer Synergy By Date Column =
SUMX(FILTER('Customer IDs w Synergy Dates', 'Customer IDs w Synergy Dates'[Customer ID] <> BLANK()),
[Synergy])
With this, I can create a bar chart, using the calendar in the main model, showing the Synergy by month. However, as I said, changing the slicers, doesn't change the view here because I am using a calculated column in a calculated table.
I then went a step further and created a line chart, showing the cumulative Synergy over time with this measure:
However, as before, due to the calculated table, any filtering on business unit or product, etc, doesn't reflect in these two visuals. So, if there's another way to do this, and have it change dynamically with filtering, I'd enjoy knowing how to do it.