How to Graph using a Time Variable using a Measure Calculated with Dates Between

[Shelley]

Hello Power BI Community,

I have a project where I have to identify customers with their first order date for a specific product and take all orders from that point out 24 months to calculate sales "synergy." I have to do this using another team's model that calculates the sales, using a measure (because it's complicated). As such, I've created a composite model.

I think I determined how to select the customers via the query editor and some DAX and I have a start and end date caculated for each customer ID. Customers selected data looks like this:

Table: Customer Synergy IDs

[Customer ID]

[Synergy Start Date]

[Synergy End Date]

Customer ID     Synergy Start Date    Synergy End Date

1234                    4/1/2024                      4/1/2026

9874                     5/15/24                        5/15/26

6487                     6/8/24                           6/8/26

I think I also determined how to calculate the Sales between dates, using this measure:

Synergy =

    CALCULATE( [Sales Measure],

    DATESBETWEEN('Date'[Date],

        MIN('Customer Synergy IDs'[Synergy Start Date]),

        MAX('Customer Synergy IDs'[Synergy End Date])))

Oh, to get the totals right on the table visual, I had to do a second measure. There's probably an easier, more efficient way to do this, but I don't have it yet.

Synergy SUMX =

    SUMX(

        FILTER('Customer Synergy IDs', 'Customer Synergy IDs'[Customer ID] <> BLANK()),

            [Synergy])

WIth this, I can list customers in a table or a bar chart by name, and use a donut chart to show total "synergy" by product, but I'm stuck on trying to determine how to use a bar chart or line chart to graph total "synergy" by month or year. For example, when I use any time column from the date table in a bar chart, Power BI graphs the total synergy in every single month as if the measure is disconnected from the dates in the data. (see below)

I've tried creating tables with every date a customer should be counted for synergy, but when I try to connect into the date table in the model with a bi-directional relationship, I receive an error. This is difficult to explain, and I cannot provide a sample file because of the size, complexity and confidentiality of this model.

Any ideas? How can I plot the Synergy value, using a time axis?

[v-yangliu-msft]

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

[Shelley]

Hello @v-yangliu-msft 
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:

Customer Synergy Over Time =

    VAR MinDate = MINX(ALLSELECTED('Customer Synergy Calendar'), 'Customer Synergy Calendar'[Date])

    VAR MaxDate = MAXX(ALLSELECTED('Customer Synergy Calendar'), 'Customer Synergy Calendar'[Date])

        RETURN

        IF(

            MAX('Customer Synergy Calendar'[Date]) >= MinDate && MAX('Customer Synergy Calendar'[Date]) <= MaxDate,

                SUMX(FILTER(ALL('Customer Synergy Calendar'), 'Customer Synergy Calendar'[Date] >= MinDate && 'Customer Synergy Calendar'[Date] <= MaxDate && 'Customer Synergy Calendar'[Date] <= MAX('Customer Synergy Calendar'[Date])),

                'Customer Synergy Calendar'[Customer Synergy By Date Column]), BLANK())

With this, I can graph a cumulative line over time:

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.