Calculating MTD, QTD, YTD from a date

[Mario36]

Given a column of dates, how can I calculate if the date is a MTD / QTD or YTD ?

[apathetichell]

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)

[Mario36]

Do you mind sharing a workflow please. This is how I wish to see the data populate

Date	MTD	QTD	YTD
1/31/2021			YTD
4/15/2021	MTD	QTD	YTD

[apathetichell]

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?

[Mario36]

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.

[apathetichell]

As discussed - here is a workflow which calculates the number of days from the start of the month, the start of the year and the start of the quarter...

[Mario36]

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.

[apathetichell · Accepted solution]

@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.

[Mario36]

@apathetichell  Thank you !!
This is exactly what I expected. You are awesome.

[apathetichell]

thanks!