Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi,
I have store sales data by reporting week, but not each reporting week has data for each store. What I want to do is create a new table by reporting week that sums the sales data of the latest entry of each store in order to create a line graph by reporting week.
ROW | STORE_KEY | Reporting Week (a) | Value | This entry rank by store |
1 | A | 201801 | 342.99 | 1 |
2 | A | 201802 | 2,608.26 | 2 |
3 | B | 201802 | 2,771.60 | 1 |
4 | B | 201804 | 10,613.88 | 2 |
5 | A | 201805 | 852.88 | 3
|
Reporting Week (b) | Value | Calculation explantion 1 | Calculation explantion 2 | Calculation explantion 3 |
201801 | 342.99 | Row 1 | 342.99 + null | only store A has entry before or equal to week 201801. No data for store B |
201802 | 5,379.86 | Row 2 + Row 3 | 2,608.26 + 2,771.60 | Row 1 data from 201801 is replaced by Row 2 data from 201802 |
201803 | 5379.86 | Row 2 + Row 3 | 2,608.26 + 2,771.60 | No new data from 201803 so latest data from 201802 is used |
201804 | 13,222.14 | Row 2 + row 4 | 2,608.26 + 10,613.88 | , Store A's latest data is from 201802 so this is used, Store B has data from 201804 which is used |
201805 | 11,466.76 | Row 4 + Row 5 | 10,613.88 + 852.88 | Store A has data from 201805 so this is used, Store B's latest data is still from 201804 |
I've been succesfull in summing the sales value if Reporting week (a) from table 1 is LT or EQ to Reporting week (b) from my calculated table but this sums all values whereas I only want to sum the latest value for each STORE KEY. I've tried using measures using MAX functions of the entry rank but have been unsuccesful. help please!
Solved! Go to Solution.
@Anonymous Well I got curious why it didn;t work, but alright so I see what is going on here. Luckily I had my PBI open I created for my last attempt. So what is happening is that generating a series of yyyyww values, doesn't take into account the max of the weeks. So, to circumvent this I've created yet another Calculated Table. In this I take the minimum reporting week from your data as the start date and the maximum reportingdate as the end date of my date table. I do some magic to create a list of yyyymm values that contains at least all values that are in your data. The formula is like this:
Dates = ADDCOLUMNS (
CALENDAR (
DATE ( VALUE ( LEFT ( MIN ( 'DataTable'[Reporting Week (a)] ), 4 ) ) , 1, 1 ),
DATE ( VALUE ( LEFT ( MAX ( 'DataTable'[Reporting Week (a)] ), 4 ) ) , 12, 30 )
),
"YearWeek",
VAR leadingZero =
IF ( WEEKNUM ( [Date] ) < 10, 0 )
RETURN
VALUE(YEAR ( [Date] ) & leadingZero
& WEEKNUM ( [Date] ))
)
So now, we are going to alter my previous DAX expression. First, we are not using GENERATESERIES anymore because this creates series like 201753 and 201799 which are obvious bad values. So instead we are going to create a distinct list of the YearWeek column, while filtering the Dates table based on the MAX and MIN of the reportingweek column. So, the VAR weeks will be this:
VAR weeks =
CALCULATETABLE (
DISTINCT ( Dates[YearWeek] ),
FILTER (
Dates,
Dates[YearWeek] >= MIN ( 'DataTable'[Reporting Week (a)] )
&& Dates[YearWeek] <= MAX ( 'DataTable'[Reporting Week (a)] )
)
)
Further more, VAR currentWeek should now refer to column [YearWeek]. The rest should stil work though. The total table DAX is now this:
Table =
VAR weeks = CALCULATETABLE(DISTINCT(Dates[YearWeek]), FILTER(Dates, Dates[YearWeek] >= MIN('DataTable'[Reporting Week (a)]) && Dates[YearWeek] <= MAX('DataTable'[Reporting Week (a)])))
RETURN
ADDCOLUMNS (
weeks, "DesiredOutput",
VAR currentWeek = [YearWeek]
VAR stores =
ADDCOLUMNS ( DISTINCT ( 'DataTable'[STORE_KEY] ), "lastValue",
VAR currentStore = [STORE_KEY]
VAR lastWeekOfStore = CALCULATE(LASTNONBLANK('DataTable'[Reporting Week (a)], 1),
FILTER('DataTable', 'DataTable'[STORE_KEY] = currentStore && 'DataTable'[Reporting Week (a)] <= currentWeek))
RETURN
CALCULATE(MAX('DataTable'[Value]),
FILTER('DataTable', 'DataTable'[STORE_KEY] = currentStore && 'DataTable'[Reporting Week (a)] = LastWeekOfStore)
)
)
RETURN
SUMX(stores, [lastValue])
)
The following are my test data set and my calculated column. This does seem to match your requirements right?
Proud to be a Super User!
@JarroVGIT I have a similar problem and I tried follow what you have described above and it seems to me, that perhaps it would not work for me or maybe I just do not follow the dax and the logic so thoroughly.
So here goes, I have a case, where I have in one fact table transaction_started (datetime column), transaction_started_date (date column), country, category and value field and each category can have multiple transactions per day, thus the latest value changes throughout the day.
To get the latest value per day I have used the following measure:
Hi @Anonymous ,
We can create a calculated column as below.
Column =
VAR ke = [STORE_KEY]
VAR week = [Reporting Week (a)]
VAR k =
CALCULATE (
MAX ( 'Table'[Reporting Week (a)] ),
FILTER (
'Table',
'Table'[STORE_KEY] <> ke
&& 'Table'[Reporting Week (a)] <= week
)
)
RETURN
'Table'[Value]
+ CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
'Table',
'Table'[Reporting Week (a)] = k
&& 'Table'[STORE_KEY] <> ke
&& 'Table'[Reporting Week (a)] <= week
)
)
For more details, please check the pbix as attached.
I've also tried your solution, it works perfectly when there's just two stores as from my example. But when I add in the other stores it doesn't look to sum correctly.
Well before I give a solution, I just want to thank you @Anonymous because this might have been to most educative experience for me in trying to solve this problem for you. I've learnt a lot figuring out all the DAX required for this and I had a blast 🙂 I even had to install DaxStudio to evaluate parts of my solution, cool stuff. Anyway, back to your question.
The answer has multiple parts to it. First off, we need to generate all weeks between the minimum week you have and the maximum week you have. Fortunately your table contains yyyyww formats which are treated as integers. To generate a list (including missing weeks!), we are creating a single column table with GENERATESERIES() function and store that in a variable:
VAR weeks =
GENERATESERIES (
MIN ( 'DataTable'[Reporting Week (a)] ),
MAX ( 'DataTable'[Reporting Week (a)] ),
1
)
This variable now contains a table with one column named "Value" and has 201801 through 201805 in it (5 rows).
The second part is easy, we are going to generate a table of the variable weeks by using ADDCOLUMNS():
RETURN
ADDCOLUMNS (
weeks, "DesiredOutput",
VAR currentWeek = [Value]
.....
This takes the single column table stored under weeks, adds a column named 'DesiredOutput' to it and then starts declaring a variable currentWeek which is equal to the current row in the column Value. This is done for brevity, because we are going to do some magic and we need to know the row context (=weeknumber). Storing it in a variable makes it easier to understand what is happinging down the road.
Now, for every row in this table, I am going to need to create a dynamic table with three columns: STORE_KEY, LastAvailableValue. This table contains each unique store (so 2 rows in your example, A and B) and for each of the stores we are going to calculate what the last available week and corresponding value is. Remember, we are calculating this in the context of the main table; we are only interested in the last available week per store where the week is smaller or equal to currentWeek. We are going to store this dynamic table in a variable as well, called stores:
VAR stores =
ADDCOLUMNS ( DISTINCT ( 'DataTable'[STORE_KEY] ), "lastValue",
VAR currentStore = [STORE_KEY]
VAR lastWeekOfStore = CALCULATE(LASTNONBLANK('DataTable'[Reporting Week (a)], 1),
FILTER('DataTable', 'DataTable'[STORE_KEY] = currentStore && 'DataTable'[Reporting Week (a)] <= currentWeek))
RETURN
CALCULATE(MAX('DataTable'[Value]),
FILTER('DataTable', 'DataTable'[STORE_KEY] = currentStore && 'DataTable'[Reporting Week (a)] = LastWeekOfStore)
)
)
Now this might seem overwhelming and to be honest, this is probably a very inefficient way of doing this. However, the outcome here is that stores is a table with two columns (STORE_KEY and lastValue) and this table is calculated for each week in the endresult table. We are now going to do a step back and realize we were actually still in calculating the 'DesiredOutput' column. By summing the 'lastValue' of the dynamic table under variable stores we are doing just that:
RETURN
SUMX(stores, [lastValue])
The total formula of your calculated table is then like this:
Table =
VAR weeks =
GENERATESERIES (
MIN ( 'DataTable'[Reporting Week (a)] ),
MAX ( 'DataTable'[Reporting Week (a)] ),
1
)
RETURN
ADDCOLUMNS (
weeks, "DesiredOutput",
VAR currentWeek = [Value]
VAR stores =
ADDCOLUMNS ( DISTINCT ( 'DataTable'[STORE_KEY] ), "lastValue",
VAR currentStore = [STORE_KEY]
VAR lastWeekOfStore = CALCULATE(LASTNONBLANK('DataTable'[Reporting Week (a)], 1),
FILTER('DataTable', 'DataTable'[STORE_KEY] = currentStore && 'DataTable'[Reporting Week (a)] <= currentWeek))
RETURN
CALCULATE(MAX('DataTable'[Value]),
FILTER('DataTable', 'DataTable'[STORE_KEY] = currentStore && 'DataTable'[Reporting Week (a)] = LastWeekOfStore)
)
)
RETURN
SUMX(stores, [lastValue])
)
This results in the desired output, under dataview like this:
Well I had a blast and I hope you could follow my explanation.
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Kudo's are welcome 🙂
Proud to be a Super User!
Hi @JarroVGIT
I really appriciate the efforts and feel this is very close to the solution but isn't quite there. For some reason when I'm entring your formulas it's not updating for each week
(apologies for the different data from the example, the numbers in my example were made up to simplify things - and I'm actually looking at losses rather than sales so they're negative numbers). The column called 'value' is called 'final annualised adj val figure' in my model.
As you can see it's assigning the result from week 201625 to 201626... which was easily fixed by subtracting 1 from your filter on row 16
....
Again really appriciate the help, this problem is a bit beyond my understanding.
@Anonymous Well I got curious why it didn;t work, but alright so I see what is going on here. Luckily I had my PBI open I created for my last attempt. So what is happening is that generating a series of yyyyww values, doesn't take into account the max of the weeks. So, to circumvent this I've created yet another Calculated Table. In this I take the minimum reporting week from your data as the start date and the maximum reportingdate as the end date of my date table. I do some magic to create a list of yyyymm values that contains at least all values that are in your data. The formula is like this:
Dates = ADDCOLUMNS (
CALENDAR (
DATE ( VALUE ( LEFT ( MIN ( 'DataTable'[Reporting Week (a)] ), 4 ) ) , 1, 1 ),
DATE ( VALUE ( LEFT ( MAX ( 'DataTable'[Reporting Week (a)] ), 4 ) ) , 12, 30 )
),
"YearWeek",
VAR leadingZero =
IF ( WEEKNUM ( [Date] ) < 10, 0 )
RETURN
VALUE(YEAR ( [Date] ) & leadingZero
& WEEKNUM ( [Date] ))
)
So now, we are going to alter my previous DAX expression. First, we are not using GENERATESERIES anymore because this creates series like 201753 and 201799 which are obvious bad values. So instead we are going to create a distinct list of the YearWeek column, while filtering the Dates table based on the MAX and MIN of the reportingweek column. So, the VAR weeks will be this:
VAR weeks =
CALCULATETABLE (
DISTINCT ( Dates[YearWeek] ),
FILTER (
Dates,
Dates[YearWeek] >= MIN ( 'DataTable'[Reporting Week (a)] )
&& Dates[YearWeek] <= MAX ( 'DataTable'[Reporting Week (a)] )
)
)
Further more, VAR currentWeek should now refer to column [YearWeek]. The rest should stil work though. The total table DAX is now this:
Table =
VAR weeks = CALCULATETABLE(DISTINCT(Dates[YearWeek]), FILTER(Dates, Dates[YearWeek] >= MIN('DataTable'[Reporting Week (a)]) && Dates[YearWeek] <= MAX('DataTable'[Reporting Week (a)])))
RETURN
ADDCOLUMNS (
weeks, "DesiredOutput",
VAR currentWeek = [YearWeek]
VAR stores =
ADDCOLUMNS ( DISTINCT ( 'DataTable'[STORE_KEY] ), "lastValue",
VAR currentStore = [STORE_KEY]
VAR lastWeekOfStore = CALCULATE(LASTNONBLANK('DataTable'[Reporting Week (a)], 1),
FILTER('DataTable', 'DataTable'[STORE_KEY] = currentStore && 'DataTable'[Reporting Week (a)] <= currentWeek))
RETURN
CALCULATE(MAX('DataTable'[Value]),
FILTER('DataTable', 'DataTable'[STORE_KEY] = currentStore && 'DataTable'[Reporting Week (a)] = LastWeekOfStore)
)
)
RETURN
SUMX(stores, [lastValue])
)
The following are my test data set and my calculated column. This does seem to match your requirements right?
Proud to be a Super User!
Sorry for the delay in replying. Took me a while to get chance to test your changes. Works great. You're a star!
Hi @Anonymous , I am sorry to hear it does cause you some issues. Would it be possible to upload the pbix file? Then I can try to make a solution for you that would definitely fit your needs 🙂 Might be that I only have time later this week though, if that is is not a problem with you or your project.
Proud to be a Super User!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
121 | |
112 | |
58 | |
57 |