cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dash_board2021
Regular Visitor

New Column for Quarters

Hello All,
I have this quarter's column (Image 1) and I am trying to make a new column against them as "Past and Future" so right now it is 2021Q3 anything including 2021Q3 should be future and anything below 2021Q3 should be labeled as past but when the data is pulled next quarter i.e 2021Q4, then 2021Q3 becomes past.

So far I added another conditional column (Image 2 and result in Image 3), but the problem with this is next quarter I will have to edit this conditional column as 2021Q3 will be Past. So I was wondering if there is a way this can be done in a better way using DAX?

 

112233

1 ACCEPTED SOLUTION
imenka
Advocate I
Advocate I

If I got your question right, you can try this:
Step 1: get quarter end date, add new column, not measure.

QEnd = IF(RIGHT('Table'[Quarter],2) = "Q1", Date(left('Table'[Quarter],4),03,31), IF(RIGHT('Table'[Quarter],2) = "Q2", Date(left('Table'[Quarter],4),06,30), IF(RIGHT('Table'[Quarter],2) = "Q3", Date(left('Table'[Quarter],4),09,30), IF(RIGHT('Table'[Quarter],2) = "Q4", Date(left('Table'[Quarter],4),12,31)))))

Step 2: add one more column to validate current date.
xPastFuture = IF(TODAY() >= 'Table'[QEnd], "Past", "Future")


Change logic as per your need hope this works for you

q.png

View solution in original post

1 REPLY 1
imenka
Advocate I
Advocate I

If I got your question right, you can try this:
Step 1: get quarter end date, add new column, not measure.

QEnd = IF(RIGHT('Table'[Quarter],2) = "Q1", Date(left('Table'[Quarter],4),03,31), IF(RIGHT('Table'[Quarter],2) = "Q2", Date(left('Table'[Quarter],4),06,30), IF(RIGHT('Table'[Quarter],2) = "Q3", Date(left('Table'[Quarter],4),09,30), IF(RIGHT('Table'[Quarter],2) = "Q4", Date(left('Table'[Quarter],4),12,31)))))

Step 2: add one more column to validate current date.
xPastFuture = IF(TODAY() >= 'Table'[QEnd], "Past", "Future")


Change logic as per your need hope this works for you

q.png

View solution in original post

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.