Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

compare values between two date

Hi all,

 

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

mbighi_1-1650445610553.png

 

 

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

 

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

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

 

vluwangmsft_0-1651047687544.png

Step 2,create slicer table:

Slicer = DISTINCT('Table'[date])

 

Step 3,enter table,then create measure:

vluwangmsft_1-1651047772117.png

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

Step4, create the below measure and create visual:

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

vluwangmsft_2-1651047826613.png

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


Best Regards

Lucien

View solution in original post

3 REPLIES 3
v-luwang-msft
Community Support
Community Support

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

 

vluwangmsft_0-1651047687544.png

Step 2,create slicer table:

Slicer = DISTINCT('Table'[date])

 

Step 3,enter table,then create measure:

vluwangmsft_1-1651047772117.png

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

Step4, create the below measure and create visual:

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

vluwangmsft_2-1651047826613.png

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


Best Regards

Lucien

Anonymous
Not applicable

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

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

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.