Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

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

ROWSTORE_KEYReporting Week (a)ValueThis entry rank by store
1A201801342.991
2A2018022,608.262
3B2018022,771.601
4B20180410,613.882
5A201805852.88

3

 

 

 

Reporting Week (b)ValueCalculation explantion 1Calculation explantion 2Calculation explantion 3
201801342.99Row 1342.99 + nullonly store A has entry before or equal to week 201801. No data for store B
2018025,379.86Row 2 + Row 32,608.26 + 2,771.60Row 1 data from 201801 is replaced by Row 2 data from 201802
2018035379.86Row 2 + Row 32,608.26 + 2,771.60No new data from 201803 so latest data from 201802 is used
20180413,222.14Row 2 + row 42,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
20180511,466.76Row 4 + Row 510,613.88 + 852.88Store 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

@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.I've added some rows that are in a earlier year, with lots of inbetween weeks.Which results in the following table..Which results in the following table..





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

8 REPLIES 8
Datafruit
Helper I
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. 
v-frfei-msft
Community Support
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
            )
        )

 

Capture.PNG

 

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

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. 

 

 

Capture.PNG

JarroVGIT
Resident Rockstar
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
    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:

image.png

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 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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. 

Capture.PNG

 

 

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)?
 
Capture.PNG
 
 
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.

 
 

@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.I've added some rows that are in a earlier year, with lots of inbetween weeks.Which results in the following table..Which results in the following table..





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

@JarroVGIT 

 

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. 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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