Suming sales between dates from one table and filters from another

[alaynanich]

HI PBI community,

I have an ask to calcuclate the total sales for stores in an event promotion, and I'm having trouble thinking of an appropriate way to use Calculate since I need values from three tables

The first table is a list of stores and the their sales per day like below

store_num	salestransaction_date	sales
3045	4/1/2023 0:00	39909.38
12233	4/2/2023 0:00	27800.62
12000	4/3/2023 0:00	14799.82
33	4/4/2023 0:00	5598.49
8229	4/5/2023 0:00	2113.54
16939	4/6/2024 0:00	2894.86
11267	4/7/2024 0:00	2081.68
16339	4/8/2024 0:00	10574.59
17568	4/9/2024 0:00	6834.87
16810	4/10/2024 0:00	6049.49

The event period information comes from a separate file. I don't necessarily need the "this year or last year" column but I will need to later do YOY sales for each month if that helps.

This Year or Last Year	Month	start	end	Event Nm
TY	APR	4/19/2024	4/25/2024	Event C
LY	APR	4/12/2023	4/15/2023	Event D

Finally, there is a seprate list for whether teh store particpated in the event or not based on whether or not they bought a sign kit for the month

Month	STORE	SIGN KIT
APR	3045	MINI
APR	12233	MINI
APR	12000	MINI
APR	33	MINI
APR	8229	FULL
APR	16939	FULL
APR	11267	FULL
APR	16339	FULL
APR	17568	FULL
MAY	16810	FULL

So I need a connection from the sales transaction date to the promotion dates, and a connection for the Month columns, and a connection for the store_num columns. This makes my data model a little complicated and not sure how I could find sales form that first table between the start and end dates, as well as identifying if they participated. I tried the model below using a date table as a "bridge", but nothing is being filtered - i think there's too many relationships. My deisred result would something like below

This Year or Last Year	Month	start	end	Event Nm	sign kit	store count	sales
TY	APR	4/19/2024	4/25/2024	Event C	MINI	5	100
TY	APR	4/19/2024	4/25/2024	Event C	FULL	6	200
LY	APR	4/12/2023	4/15/2023	Event D	MINI	7	300
LY	APR	4/12/2023	4/15/2023	Event D	FULL	8	400

[alaynanich]

@scee07 This is amazing!! Thank you so much for the throrough reply and explanation. I tweaked the calculated column solution and it works perfectly for what I need!

[scee07 · Accepted solution]

From what I understood from your problem, I will formulate my guess on the business rules:

• Every promotion has a unique name
• You can assign a month to a promotion (either by a start date or end date of the promotion). This means that you can assign it to a fiscal month
• The kit table implies that you can subscribe for the month for a promotion with the sizes MINI and FULL.
• If a store subscribes, they subject to all promotions of this month.

This might be matching your case or not. In any case it gives me a baseline for the considerations and can be induced from the given data.

As always, there is a calculated columns approach and a measure approach.

Calculated columns approach

In this approach the sales table gets additional columns. As a result, one master table is created containing all information:

Pros:

• All visuals use only this table, and the rendering of the data is the quickest, as all calculations are done in the model refresh
• It is not necessary to build up a relationship model. This is always better in terms of data integrity and processing times, but you can calculate everything in the columns without having that.

Cons:

• Increased use of memory: with the creation of calculated columns memory for storing the table in RAM is increased and the optimization process of the Vertipaq engine is potentially negatively influenced.
• Data refresh times increase

As always everything depends on the size and structure of the data model.

At the bare minimum, the sales table has the columns transaction date, store number and sales amount.

To find the promotion, we use the fact that we have a unique month for the promotion as assumed and can find a kit for the month or not.

In the same way we can find the kit:

Start and end date work the same way.

Measure approach

This approach targets the visual directly, uses less memory. How fast this renders, depends on your model.

If you use the table visual you still need a base table reference:

You can now build up a proper relationship model for the base filters:

Now, if you have the base elements in the table visual you can define measures:

Now, eventStartDate, eventEndDate, countSalesLines and salesAmount are all measures.

The big disadvantage is now that you have a fixed aggregation level and would have to use calculation groups to make this efficient if aggregation hierarchies are in scope.

As always, all speculation based on some information given.

Dr. Christian Scheeren

www.scheeren-bi-consulting.com