Forum Discussion

balu810's avatar
balu810
Advocate I
4 years ago
Solved

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 i...
Need Help
Show and Tell
  • parry2k's avatar
    parry2k
    4 years ago

    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's avatar
VijayP
Community Champion
4 years ago

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's avatar
balu810
Advocate I
4 years ago

I am sorry its not what i am looking for 

 

Project DateRevenue
Project 1 04-11-2021100
Project 1 06-12-2021120
Project 1 05-07-2022130
Project 2 03-03-2022250
Project 2 08-06-2022270
project 2 08-08-2023500

 

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