Categorize date difference in periods (1 month, 3 month, 6 month)

[Anonymous]

Hello! 

I hope someone can send me in a right direction 🙂

I have a table like this:

ID, Product, SalesDate, OfferExpirationDate

1, Chair, 10/1/2020, 11/1/2020

2, Table, 09/1/2020, 02/1/2021

I need to calculate if two dates are within specific period from each other (within 1 month, 3 month, 6 month).

Example:

ID 1: 10/1/2020 - 10/31/2020  = 30 days which within 1 month period so my column should have "1 month" value

ID 2: 09/1/2020 - 02/1/2021  = 153 days which within 6 month period so my column should have "6 month" value.

I know how to calculate date difference in days:

OfferSalesDays = ABS(DATEDIFF(SalesDate, OfferExpirationDate, DAY))

but I can't find how to "categorize" results in specific date range 1 month, 3 month, 6 month so i can use it as a column or filter.

Any advice is much appreciated!
Thank you!

[amitchandak · Accepted solution]

@Anonymous ,

OfferSalesDays = Quotient(ABS(DATEDIFF(SalesDate, OfferExpirationDate, DAY)),30)

or

OfferSalesDays = round(divide(ABS(DATEDIFF(SalesDate, OfferExpirationDate, DAY)),30),0)

or

OfferSalesDays = ABS(DATEDIFF(SalesDate, OfferExpirationDate, month))

[negi007 · Accepted solution]

@Anonymous You can create a column like below

In Months = ROUND(( Product_furniture[ OfferExpirationDate]-Product_furniture[ SalesDate].[Date])/30,0) & " Month"

[amitchandak · Accepted solution]

@Anonymous ,

OfferSalesDays = Quotient(ABS(DATEDIFF(SalesDate, OfferExpirationDate, DAY)),30)

or

OfferSalesDays = round(divide(ABS(DATEDIFF(SalesDate, OfferExpirationDate, DAY)),30),0)

or

OfferSalesDays = ABS(DATEDIFF(SalesDate, OfferExpirationDate, month))

[Anonymous]

@amitchandak @negi007 Thank you for your suggestions! It's closer to what i'm looking for but, if i understand you correctly, OfferSalesDays will contain number of month/30-day periods between two dates.

My final goal is to have a drop-down filter with just "1 month", "3 month", "1 year". I don't want my user to pick exact number of month but just a range/period.

Thank you!

[negi007]

@Anonymous Once you create calculated column, you can always use in slicer to select values. It will work for you.

[Anonymous]

@negi007 @amitchandak 

Thank you! i've used your suggestion to calculate difference in months and then added SWITCH to set periods:

Here is what i'll use:

C1 = SWITCH(ABS(DATEDIFF(SalesDate, OfferExpirationDate, MONTH)), 1, "1 month", 2, "3 month", 3, "3 month", 4, "6 month", 5, "6 month", 6, "6 month", "year+")

Thank you again for your help!

[amitchandak]

@Anonymous ,

You can create column like

OfferSalesDays = round(divide(ABS(DATEDIFF(SalesDate, OfferExpirationDate, DAY)),30),0) & " month"

and use that in slicer