Sales per square feet based on store opening date


#1

Hi Sam, First of all…i have to say you doing a really great job.

The Case: I have list of 20 stores which opened on various dates starting from 2005, in various countries. I am preparing the financial reports in power bi for the period 2017 and 2018. My datetable is set for the 2017-18 period.
I am trying to compute the sales per square feet for is 2017 & 2018 by country/store. However, i am unable to get the cumulative logic on the outlet area correctly [since the opening dates are outside the scope of the datetable, i have not created a relationship between opening dates and datetable ]

the relevant data is in two tables as follows:
i.) Outlet area table showing Outlet name, Opening date and outlet area
ii.) Sales table with monthly sales from 2017 onwards, outlet name, country name


#2

Hi Jackson.

Will just need to see more detail here to assist.

Will need to see the data, data model, DAX measures you currently have, how you want to represent the data in a table. All of this matters.

You should also always have a relationship between your date table and your fact table. That allows you to utilise all the cumulative patterns that are showcased.

Also to me it looks like you could merge these two tables to make this very easy to complete. You could merge these tables in the query editor based on Outlet name. That means you would have one simple table to run all this particular logic.

I don’t actually believe this is too difficult. It’s just about getting the set up right and then using the correct formula pattern.

See below for that.