Caluclated Column for Year

[balu810]

I have project start date in one table and i created date table which have continuous dates and i extrated year out of it . 

I have several projects which have which starts in one year and ends in another year . 

for example 

project 1 starts in Nov-2021 ends in july 2022 

project 2 starts in Mar -2022 ends in August 2023

I am using matrix table to show data with year in columns and projects in rows . 

when nothing is selected in projects filter i am seeing Year 2021 , 2022 , 2023 and their respective data for each project . 

My requirement is when someone filter only one project - the minimum year of that year should be shown as Year 1 , sucedding years as Year 2 , Year 3 ... till the data presisits for that project . 

in the above example 

when i select Project 1 in filter , it has data for 2021 , 2022 , in my matrix visual i would like to see 2021 as Year 1 and 2022 as Year 2 .

when i select Project 2 in filter , it has data for 2022 , 2023 , in my matrix visual i would like to see 2022 as Year 1 and 2023 as Year 2 .

FYI.. i have many projects above are just sample data to explain usecase , i cant hardcode the logic . 

@v-easonf-msft 

@amitchandak 
@johnt75 
@bcdobbs 

@parry2k 

@MFelix 

@Ashish_Mathur 

@JihaiXia 

@Jihwan_Kim 

@dm-p 

@KerKol 

@VahidDM 

@v-syr 

@v-sree 

@v-saia 

@Anonymous 

@PC2790 

@v-easonf-msft 

@v-eachen-msft 

@v-xiaotang 

@V-lianl-msft 

[parry2k · Accepted solution]

@balu810 add a new column for Year Cohort using the following DAX expression and then use the new column on columns in matrix visual.

Cohort Year = 
VAR __startYear = CALCULATE ( MIN ( 'Table'[Date] ), ALLEXCEPT ( 'Table','Table'[Project ] ) )
RETURN DATEDIFF ( __startYear, 'Table'[Date], YEAR ) + 1

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[VijayP]

@balu810 

you need to begin your measure with 

IF ( ISBLANK( measure) , BLANK(), Mesure)

so you need to notify explicitly that if values are missing the data should not be shown and even the relavant years

Try This and let me know!

[balu810]

I am sorry its not what i am looking for 

Project	Date	Revenue
Project 1	04-11-2021	100
Project 1	06-12-2021	120
Project 1	05-07-2022	130
Project 2	03-03-2022	250
Project 2	08-06-2022	270
project 2	08-08-2023	500

This is my data 

Currenly output when no selection on Project Name :

in below picture , in place of 2021 i need Year 1 as it is the minimum year , 2022 as year 2 , if the data comes for next year it will be year 3 

in below picture , in place of 2022 i need Year 1 as it is the minimum year , 2023 as year 2 , if the data comes for next year it will be year 3