Calculate the Change of Values by Dates

[felixzhao123]

Hi，

I have a table in my dataset that contains the product stock. What i want to achieve is to calculate the stock value change by product category based on the date range selected by user with date filter visual. The difference is between the stock value by category in the most recent date selected and oldest date selected.

The table looks like this:

I tried with SUMMARIZECOLUMNS and FILTER but not returning what I expected.

Thanks in advance,

Felix

[v-linyulu-msft]

Hi @felixzhao123 ,

Regarding the issue you raised, my solution is as follow：

1. First of all I have created a table based on the table you have provided, the table name is “report' and the structure of the table is shown below:

2. As I understand it, your filtering criteria are only time and category, so I created this measure below:

First I got the maximum and minimum values of the dates respectively, then I used the max function to determine the values of the same group, and the date is the maximum or minimum value, and subtracted the values of the inv columns to come up with the final answer.

Measure = 
var _mindate=
MINX(FILTER(ALLSELECTED('Table'),'Table'[castgory]=MAX('Table'[castgory])),[Date])
var _maxdate=
MAXX(FILTER(ALLSELECTED('Table'),'Table'[castgory]=MAX('Table'[castgory])),[Date])
var _minvalue=
SUMX(
    FILTER(ALL('Table'),'Table'[castgory]=MAX('Table'[castgory])&&'Table'[Date]=_mindate),[inv￥])
var _maxvalue=
SUMX(
    FILTER(ALL('Table'),'Table'[castgory]=MAX('Table'[castgory])&&'Table'[Date]=_maxdate),[inv￥])
return
_minvalue - _maxvalue

3. Here's my final result, which I hope meets your requirements.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[felixzhao123]

Thanks for your help! Would you please help me understand why in this statement:

MAXX(FILTER(ALLSELECTED('Table'),'Table'[castgory]=MAX('Table'[castgory])),[Date])

we need 'Table'[castogry]=max('Table'[castgory])?

Thanks!

Felix

[v-linyulu-msft · Accepted solution]

Hi,@felixzhao123 

Here's my explanation of how I use my functions:

1.It works by using category as the grouping condition, then the ALLSELECTED() function retains this filtering condition, and finally MAXX() counts the maximum value within this group.

2.To make it easier for you to understand, I have created the following table:

There are two more measure:

Measure 1 = MAX('Table'[Column1])

Measure 2= CALCULATE(SUM('Table'[Column2]),FILTER(ALLSELECTED('Table'),'Table'[Column1]=MAX('Table'[Column1])))

Here is the output:

You can clearly understand in measure1 that the max function returns either A or B in that column, which is the grouping condition mentioned in our previous formula.

Measure2, on the other hand, is a simple use case to add up the values of the same group.

3.In short, the purpose of this formula is to find the rows with the largest dates in each category in the user-selected context, and then calculate the maximum value of the date field for those rows. This approach is useful when analyzing time series data or when you need to group comparisons based on categories.

4.In the DAX function, the ALLSELECTED() function serves to remove the context filters for columns and rows from the current query while retaining all other context filters or explicit filters. This function can be used to get visual totals in a query. Unlike the ALL() function, the ALL function returns all rows in the base table or all base columns with different values, ignoring any filters that may have been set on the table or the specified column.

5.Here is the introductory document, hopefully this will help you understand what this formula does:

MAX function (DAX) - DAX | Microsoft Learn
ALLSELECTED function (DAX) - DAX | Microsoft Learn

MAXX function (DAX) - DAX | Microsoft Learn

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.