Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Good Afternoon,
I have been struggling with the following issue, I do not know if it's possible to do it in PowerBI/DAX.
My data looks like:
Date Continent Country Company Operations
01/01/2017 Europe Spain Google 500
01/01/2017 Europe Spain Apple 250
01/01/2017 Europe France Google 300
01/01/2017 Europe France Microsoft 400
01/01/2017 Europe Italy Google 100
02/01/2017 Europe Spain Google 300
02/01/2017 Europe Spain Apple 100
.
.
.
And I would like to add a column that correponds to the difference between consecutive days for the same company in the same country (for example, 500 (Google/Spain/01.01.2017) - 300 (Google/Spain/02.01.2017) = 200) and this 200 should be positioned in the first row of the next column (in our example it would look like)
Date Continent Country Company Operations Difference
01/01/2017 Europe Spain Google 500 200
01/01/2017 Europe Spain Apple 250 150 (250-100)
01/01/2017 Europe France Google 300 .
01/01/2017 Europe France Microsoft 400 .
01/01/2017 Europe Italy Google 100 .
02/01/2017 Europe Spain Google 300
02/01/2017 Europe Spain Apple 100
Is this possible?
The formula that I have tried is the following:
[Difference] = CALCULATE (
SUM( 'Sheet1'[Operations] );
FILTER(ALL('Sheet1' );
'Sheet1'[Date] <= EARLIER ('Sheet1'[Date] )
&& 'Sheet1'[Country] = EARLIER( ('Sheet1'[Country]) )
&& 'Sheet1'[Company] = EARLIER('Sheet1'[Company])
))
But I am only able to sum the values between the different company/country/consecutive dates, not being able to perform the difference.
I would be really grateful if someone can help me.
Thanks!!
Solved! Go to Solution.
Hi. Try this calculated column
= VAR FollowingDay = NEXTDAY ( Sheet1[Date] ) RETURN Sheet1[Operations] - CALCULATE ( VALUES ( Sheet1[Operations] ), FILTER ( ALL ( Sheet1 ), Sheet1[Company] = EARLIER ( Sheet1[Company] ) && Sheet1[Country] = EARLIER ( Sheet1[Country] ) && Sheet1[Date] = FollowingDay ) )
PD.I would like to Point that there are many companies and countries (what I put was a smaller example in the post) and it is not feasable to put manually which company in which country must be searched by the function (that is why I used the "EARLIER" function)
Hi. Try this calculated column
= VAR FollowingDay = NEXTDAY ( Sheet1[Date] ) RETURN Sheet1[Operations] - CALCULATE ( VALUES ( Sheet1[Operations] ), FILTER ( ALL ( Sheet1 ), Sheet1[Company] = EARLIER ( Sheet1[Company] ) && Sheet1[Country] = EARLIER ( Sheet1[Country] ) && Sheet1[Date] = FollowingDay ) )
Great solution! Thanks Man.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |