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

Dynamic Columns and Grouping: Getting X year from a list of years

Struggling to find ways of maniplating dataframes dynamically. 

When pivoting a column, is there any way of keeping track of how many columns you have created? Or is there any way of tracking the 'position' of a date value within a list (e.g. 'the date value 31/03/2022 is the third value in the list sequentially')

 

My Fact table has a column for sales year (always 31st March 20XX), and another column for number of products sold. I pivot this to get these dates as columns and sales as values, so i can then replace any null values for the next part of my calculation, where i unpivot back again so each product has a row regardless if it has a sale in that year or not (I do it this way for a cummulative calculation). 

Later i want to be able compare these figures to a rolling target. Say our sales target for 2030 was 1,000, i would then divide the target by however many years to get a position we should ideally be in at a particular year (e.g. at year '3', it should be 3 x 10/1000). What i'm stuck on is how to get to "there are 6 dates in the column, X is the third date in the list sequentially' so i can then apply the target calculation.


Any ideas?


Thanks 🙂  

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

Hi, @GlassShark1 

According to your original thread description, I guess you want to track the 'position' of a date value within a list, I think you can achieve this by adding an index column in the Power query editor, like this:

v-robertq-msft_0-1605840365881.png

Before adding the index column, you should sort ascending to make sure the date column is in positive order.

 

If you still have a problem, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-robertq-msft
Community Support
Community Support

Hi, @GlassShark1 

According to your original thread description, I guess you want to track the 'position' of a date value within a list, I think you can achieve this by adding an index column in the Power query editor, like this:

v-robertq-msft_0-1605840365881.png

Before adding the index column, you should sort ascending to make sure the date column is in positive order.

 

If you still have a problem, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

GlassShark1
Helper III
Helper III

Sorry - what i'm trying to do as a whole is quite complicated. 

For this bit though, i'm basically asking how in a column of dates (essentally years), i can 'rank' them - so '1' is the earliest, '2' the next, etc etc.

GlassShark1_0-1605695864800.png


Annoyingly thinking about it, i can probably do this with RANKX?

 

Thanks 🙂

amitchandak
Super User
Super User

@GlassShark1 ,The information you have provided is not making the problem clear to me. Can you please explain with an example. Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Appreciate your Kudos.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.