Skip to main content
cancel
Showing results for 
Search instead 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

Reply
alaynanich
Frequent Visitor

Suming sales between dates from one table and filters from another

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_numsalestransaction_datesales
30454/1/2023 0:0039909.38
122334/2/2023 0:0027800.62
120004/3/2023 0:0014799.82
334/4/2023 0:005598.49
82294/5/2023 0:002113.54
169394/6/2024 0:002894.86
112674/7/2024 0:002081.68
163394/8/2024 0:0010574.59
175684/9/2024 0:006834.87
168104/10/2024 0:006049.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 YearMonthstartendEvent Nm
TYAPR4/19/20244/25/2024Event C
LYAPR4/12/20234/15/2023Event 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

MonthSTORESIGN KIT
APR3045MINI
APR12233MINI
APR12000MINI
APR33MINI
APR8229FULL
APR16939FULL
APR11267FULL
APR16339FULL
APR17568FULL
MAY16810FULL

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

alaynanich_0-1718155445023.png

This Year or Last YearMonthstartendEvent Nmsign kitstore countsales
TYAPR4/19/20244/25/2024Event CMINI5100
TYAPR4/19/20244/25/2024Event CFULL6200
LYAPR4/12/20234/15/2023Event DMINI7300
LYAPR4/12/20234/15/2023Event DFULL8400
1 ACCEPTED SOLUTION
scee07
Resolver I
Resolver I

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.

 

scee07_6-1718181443908.png

In the same way we can find the kit:

 

scee07_7-1718181482359.png

 

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:

scee07_8-1718181515944.png

 

 

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

scee07_9-1718181546447.png

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

scee07_10-1718181577507.png

 

 

scee07_11-1718181577508.png

 

 

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

 

 

View solution in original post

2 REPLIES 2
alaynanich
Frequent Visitor

@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
Resolver I
Resolver I

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.

 

scee07_6-1718181443908.png

In the same way we can find the kit:

 

scee07_7-1718181482359.png

 

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:

scee07_8-1718181515944.png

 

 

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

scee07_9-1718181546447.png

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

scee07_10-1718181577507.png

 

 

scee07_11-1718181577508.png

 

 

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

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors