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

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

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

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors