Sum the latest value for a category by date

[Anonymous]

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!

[Datafruit]

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

[v-frfei-msft]

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.

[Anonymous]

Hi @v-frfei-msft 

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. 

[JarroVGIT]

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 🙂

[Anonymous]

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

....

    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.

[JarroVGIT · Accepted 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?

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

[Anonymous]

@JarroVGIT 

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

[JarroVGIT]

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.