Forum Discussion
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?
123
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
1 Reply
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