Given a column of dates, how can I calculate if the date is a MTD / QTD or YTD ?
Solved! Go to Solution.
You need two columns of date - no?
For mtd datetimediff([date],[date2],"months")<1 (less than 1 month)
For qtd floor(datetimediff([date],[date2],"months")/3)<1 (less than 3 months)
for ytd datetimediff([date],[date2],"years")<1 (less than 1 year)
Are you aggregating financial data? or do you want date count? Ie the number of days that have passed since the first of the year?
So I want to know if the given date on a column is either a Month To Date, Quarter To Date or Year To Date. This solution would also be used to aggregate financial data, because I would be bucketing the data based on these time periods.
I guess you've misunderstood my question. I would want to identify if a given date is a MTD, QTD or YTD.
Once Iam able to identify that, I will then proceed in grouping the dates based on these time periods. The screenshot below is how I wish to see my output.
@Mario36Hi! It would have really really helped had you provided that data initially - or the other time I asked for clarification on what you were looking for.
It's fairly straight-forward change from the previous workflow - so here's the updated version. You can add in the static columns ("MTD",QTD") as you wish.
@apathetichell Thank you !!
This is exactly what I expected. You are awesome.
thanks!