cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Datagulf
Resolver I
Resolver I

Date Table Creation Queries

Hi community.
I am creating a Date Table that I do not require to always input the columns but one stack of code. As I was creating it, I had a problem which was referencing other columns in a new column. 
It became quite hard to use quoted columns above in the same code.
The pbix with the Date table with the errors is here. Thanks.
 Date pbix file 

2 ACCEPTED SOLUTIONS
v-janeyg-msft
Community Support
Community Support

Hi, @Datagulf 

 

I checked your code, and saw you use 'addcoumn' function to create your date table, you should know that the new columns are side by side, so you can’t refer to one column from another.

vjaneygmsft_1-1634886866093.png

You have two ways, one is to use addcolumn nested, so that the following columns can refer to the column names in the previous table.

vjaneygmsft_0-1634886811493.png

The second is to create a new column in the form of a calculated column.

vjaneygmsft_3-1634887107909.png

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.


Best Regards,

Community Support Team _ Janey

View solution in original post

4 REPLIES 4
v-janeyg-msft
Community Support
Community Support

Hi, @Datagulf 

 

I checked your code, and saw you use 'addcoumn' function to create your date table, you should know that the new columns are side by side, so you can’t refer to one column from another.

vjaneygmsft_1-1634886866093.png

You have two ways, one is to use addcolumn nested, so that the following columns can refer to the column names in the previous table.

vjaneygmsft_0-1634886811493.png

The second is to create a new column in the form of a calculated column.

vjaneygmsft_3-1634887107909.png

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.


Best Regards,

Community Support Team _ Janey

View solution in original post

amitchandak
Super User
Super User

@Datagulf , Date is the only column you have available for add columns to use. All your calculation should be based on that.

 

Not very clear what you want to do there, But have the same var there in other columns and try.

 

example

 

"Day",VAR Current_Year = RIGHT(YEAR([Date]),2)
VAR Last_Year = RIGHT(YEAR([Date])-1,2)
VAR Next_Year = RIGHT(YEAR([Date])+1,2)
VAR Fiscal_Year =
    IF(
        MONTH([Date]) >= 4,
        Current_Year & "/" & Next_Year,
        Last_Year & "/" & Current_Year
    )
RETURN  FORMAT ( [Date], CONCATENATE("dd-ddd-mmm ", Fiscal_Year)),
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

@amitchandak What I am trying was to use already quoted columns to create other columns but I guess from your comment, that is impossible and one can only use the Date Column in reference to creating other columns, right?

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.