cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Comparison between values in another row

Hi guys, I'm trying to get the same values of the last column, called "SUM GROUP", but in a different order condition, and fulfill its values in column "IF SUM". As you can see, I have two months column:


tegma prob.JPG

1) one with the current month "MES_DISPOTIVO"
2) The other with the current month + 6 (months), for example: month [1] (jan) + 6 = month [7] (Jul)
3) Last there's a column called "COUNT" which I have to cross the values with "SUM GROUP" So in other words what Im trying to do is exactly the same as "IF SUM" column, but not all the values are appearing with this formula, I even tried firstnonblank.


For example:
So basically Im trying to get, when month is equal to 1, add +6 months, which will be for example the "MES_DISPOTIVO" with a value of seven (1+6) = 7, and cross the value that i got in the column "sum group" line 1 = 127, but I want this in month 7, so six months after my column month, I need this in WHOLE "IF SUM" column, theses values fulfilled. 

However not all the values are appearing because I cant match the months comparison, We are in month 8, so I'm not getting the comparison of "MES_DISPOTIVO", despite the support column all values with +6 added, in the current month we are in month 8, so I cant cross for example the line where the "MES_DISPOTIVO", value is equal 3 and "MES_DISPOTIVO" = 9, because we are in month 8, so I need to figure out another way to do it:
I've already did it in another support table, but I couldn't match the values of the column "COUNT" that I also have to compare and subtract.  (the column "COUNT" have to be kept without changing its sort order, it doesnt need modification).

 

To put it simply, I just needed to subtract in each month, the count of six months ago. So in July I need to subtract the count of January. So the challenge here is how to show the count of january in July's row. Keeping in mind that 

 

Thanks a lot for the attention!

2 REPLIES 2
Super User IV
Super User IV

@renanc , Can you share sample data and sample output in table format?

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

hey @amitchandak 
I made a sample with everything that I got for this problem with a PBI desktop, even the calculated columns.
I did some trials that almost did the job in SUP table, but the COUNT also changed its order, so it was irrelevant =/

renanc_0-1596823122361.png
The table with original data is: "vw_TG_Sample"

 

Anyway, the link for download with pbi content:
https://drive.google.com/file/d/1-DrBkkATXkdu2TAZ0wFxc5uqyhvqXkeU/view?usp=sharing 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors