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

 

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

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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.

     

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

     

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    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