cancel
Showing results 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

Anonymous
Not applicable

## Sum the latest value for a category by date

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!

1 ACCEPTED SOLUTION
Resident Rockstar

@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",
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
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?

I've added some rows that are in a earlier year, with lots of inbetween weeks.Which results in the following table..

Proud to be a Super User!

8 REPLIES 8
Helper I

@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:

Measure  = CALCULATE(FIRSTNONBLANK('Table'[value],""),FILTER('Table',MAX('
table'[les_transaction_started]))).

When using that measure in a table and having category as the second column I get per category correct results. But when I want to display the same measure on a line chart with country as legend and started_date as axis, then the value displayed in only 1 latest transaction value for one category. Even though I would want to have this summed for all categories for that day.

I sure hope that it made sense, what I wrote.
Community Support

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.

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

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.

Resident Rockstar

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
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
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!

Anonymous
Not applicable

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

....

VAR lastWeekOfStore = CALCULATE(LASTNONBLANK('Stockloss Reporting Dynamic Date table'[WEEK_ID], 1),
FILTER('Stockloss Reporting Dynamic Date table', 'Stockloss Reporting Dynamic Date table'[Branch Key Real] = currentStore && 'Stockloss Reporting Dynamic Date table'[WEEK_ID] -1 <= currentWeek))

....

however the bigger problem is your calculated table only looks to be working for the first entry of each store, I suspect the MAX() of my value (or 'FINAL ANNUALISED ADJ VAL FIGURE') might be the problem. Should we not be taking some form of latest (I understand the filter should be doing this but doesn't look to be working)?

Additionally to complicate matters I would like to be able to apply breakdowns to the values based on a FACT table which has a relationship on STORE_KEY (Mainly geographical properties) - If this over complicates things not a problem I can create a seperate calculated table for each of the breakdowns.

Again really appriciate the help, this problem is a bit beyond my understanding.

Resident Rockstar

@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",
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
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?

I've added some rows that are in a earlier year, with lots of inbetween weeks.Which results in the following table..

Proud to be a Super User!

Anonymous
Not applicable

Sorry for the delay in replying. Took me a while to get chance to test your changes. Works great. You're a star!

Resident Rockstar

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!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors