compare values between two date

[Anonymous]

Hi all,

I am trying to compare the values of the different products between two different date.

I have the above table.

Through a slicer I define the date range (we suppose the selected range is 1/11/2021 - 31/3/2022 so the initial reference date is the first date, 1/11/2021).

I need to link, per product, the values of the reference date and the "moving" date.

The goal is to calculate the sum of values and the sum of reference values (excluding the lines where one of values is blank), and the ratio between the two sums (see the above picture), in total and per month.

Do you have some suggestion? Thanks for your support

[v-luwang-msft · Accepted solution]

Hi @Anonymous ,

Based on the data you provided, try the following.

Step1,

New column:

Year = YEAR('Table'[date])

Month = MONTH('Table'[date])

new measure:

sumfact = CALCULATE(SUM('Table'[value]),ALLEXCEPT('Table','Table'[Year],'Table'[Month]),FILTER('Table','Table'[product]<>"X3"))

ref = CALCULATE(SUM('Table'[value]),FILTER(all('Table'),'Table'[date]=SELECTEDVALUE(Slicer[date])))

Step 2,create slicer table:

Slicer = DISTINCT('Table'[date])

Step 3,enter table，then create measure:

q1 = [ref]*MAX(TABLEhelp[count])

Step4, create the below measure and create visual:

test = MAXX(FILTER(TABLEhelp,TABLEhelp[q1]<=[sumfact]),TABLEhelp[q1])

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

Best Regards

Lucien

[Anonymous]

Thanks for the quick reply, I will try to explain better.

Consider the above table.

We have 3 products (x1, x2, x3) sold in those date with those values.

We suppose the initial reference date is 1/11/2021 (based on a slicer selection). In that (reference) date, we have sold x1 and x2 for these (reference) values: 100 and 110 (sum = 210).

As you can see in the table on the right, in November the refernce values (210) is equal to the November values, so the ratio is 1.

Then you pass to December values.

You have to make the sum of values of x1 and x2 (101 + 111 + 102 + 112 = 426) for December, the sum of reference values of x1 and x2 (100 + 110 + 100 + 110 = 420) and the ratio. (As you can notice, in December we have sold also x3 but it has to be excluded not been sold in the reference period).

Same procedure for all the months.

[amitchandak]

@Anonymous , Please explain the calculations

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.