Forum Discussion
Caluclated Column for Year
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 .
Anonymous
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.
3 Replies
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.
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
