How can i number rows in a table by year?

[GRedhead]

How can I number rows in a table by year?

i.e.

Test  1/1/2021      Row 1

Test  1/9/2021      Row 2

Test  1/1/2022      Row 1

Test  2/2/2022      Row 2

Any suggestions are appreciated.

[GRedhead]

Just tried that and get the following, it still is not totaling up the records each month and is instead showing the total for each month individully. 😞

I really appreicte your help on this so far.

I have the Pbix but not sure how to uplaod here.

[GRedhead]

Hi @Ashish_Mathur and @TheoC ,

I have fixed the column to start at April thanks Theo (I created a new calendar table for my fiscal year and linked the account to it by the created on) and have added a YTD field and get the following.

I just need to now have a continuous line counting the number of accounts going up each month by year.

My table just lists all the created on dates and the account details, so i want to count the number of accounts each month by year and see the number go up each month. i.e. Jan 2022 '10' Feb 2022 '15' (the 10 from January plus the 5 new added in February, and so on...) then have a line shown in the chart per year.

[TheoC]

Hi @GRedhead 

Okay, add two measuers:

Measure 1 = COUNTROWS ( Table )

Measure 2 = CALCULATE ( [Measure 1] , DATESYTD ( Dates[Date] ) l

Hopefully this will help get you the outcome! Add Measure 2 to the visual.

Let me know how it goes!

Theo 🙂

[GRedhead]

Hi @TheoC 

Just tried that and get the following, it still is not totaling up the records each month and is instead showing the total for each month individully. 😞

I really appreicte your help on this so far.

I have the Pbix but not sure how to uplaod here.

[TheoC]

Hi @GRedhead 

You should be able to share the PBIX via OneDrive, DropBox, Google Drive or another similar platform. 

Thanks in advance. 

Theo

[GRedhead]

Link to One Drive source file now removed, message below is the solution.

[TheoC · Accepted solution]

Hi @GRedhead 

Okay, change your Measure 1 to the following:

Measure 1 = DISTINCTCOUNT ('accounts (KPI Page)'[accountid] )

Then, change your Measure 2 to the following:

Measure 2 = CALCULATE ( [Measure 1] , DATESYTD ( 'FC Calendar'[Date] , "31/3" ) )

 Make sure to drag your Measure 2 to the Values section like below:

Also, change your Scalar Type to Linear (it was on Log):

Hope this helps mate!

Theo 

[Icey]

Hi @GRedhead ,

Do you mean something like below?

Steps:

1. Create columns.

FY = 
IF (
    MONTH ( [Date] ) >= 4,
    "FY " & RIGHT ( [Year], 2 ),
    "FY "
        & RIGHT ( [Year] - 1, 2 )
)

MonthName = FORMAT([Date],"mmmm")

MonthOrder = 
IF ( MONTH ( [Date] ) >= 4, MONTH ( [Date] ) - 3, MONTH ( [Date] ) + 9 )

2. Sort "MonthName" column by "MonthOrder" column.

3. Create a line chart.

Best Regards,

Icey

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

[Icey]

Hi @GRedhead ,

Do you have any comments based on my and @TheoC 's solution?

Best Regards,

Icey

[TheoC]

Hi @GRedhead 

You can use a Calculated Column to get the output you need.  If it's only two years, then you can use an IF statement:

Column = if ( RIGHT ( DATES[MyDate] , 4 ) = "2021" , "Row 1" , IF ( RIGHT ( DATES[MyDate] , 4 ) = "2022" , "Row 2" , "Row 3" ))

Hope this helps! 🙂

Theo

[GRedhead]

Thanks for the reply, and if it runs over a number of years and has a varying number of rows per year?

[TheoC]

From there, I recommend using Power Query.  Conditional Columns are very easy and no code required.

And this is the actual Conditional Column interface:

[GRedhead]

The above is not working for me.
My table has dates from yesterday back to 2014 
I want to add a column that counts up by each row in each year, so I can make a line chart starting at 1 each year and going up over the year as below.

Thanks again for the help so far

[TheoC]

@GRedhead apologies but I'm not understanding exactly what you're wanting when comparing the comments to the original issue? Can you provide a bit more detail please?

[GRedhead]

Hi, I mean I have a table that has data from today going back to 2014 for opportunities which I want to represent as follows, showing the number of records counting up from 0 through the year, I am unsure how to do this when it's all in one table.

[TheoC]

Put the Date column as the X Axis on your chart, nor the Month column. Then drag the new column you created as the legend. Then drag the new column on to the Y Axis as well. Hopefully that gives you what you are trying to get to?

[TheoC]

@GRedhead did you try to put the Date column as the X Axis on your chart, not the Month column. Then drag the new column you created as the legend. Then drag the new column on to the Y Axis as well. Hopefully that gives you what you are trying to get to (you can use the new column as the Legend too in order to split the lines).

Make sure that you set the X Axis to "Continuous" like below.  

[GRedhead]

Hi, when i try this i get the follwoing 

i want it to look like this

It's really stating to annoy me now. 😞 any additonal advice you can provide is apprecaited.

[TheoC]

@GRedhead you need to put Month Name on X Axis and YEAR in the Legend field. Hope this helps! Theo 😃

[GRedhead]

Hi, when i try that i get the following

How can i get it to start each year at 0 and count the records going up each month through the year? as per the image i posted previously sorry? apologies not sure i was explaining myself properly.