Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

compare values between two date

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 sele...
Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    4 years ago

    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's avatar
Anonymous
Not applicable
4 years ago

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.