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

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