cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

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

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.

Thank you!

2 ACCEPTED SOLUTIONS
Super User

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

Community Champion

@Anonymous You can create a column like below

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

Proud to be a Super User!

6 REPLIES 6
Community Champion

@Anonymous You can create a column like below

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

Proud to be a Super User!

Super User

@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
Not applicable

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

Community Champion

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

Proud to be a Super User!

Anonymous
Not applicable

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!
Super User

@Anonymous ,

You can create column like

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

and use that in slicer

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors