Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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!
Solved! Go to 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 You can create a column like below
Proud to be a Super User!
@Anonymous You can create a column like below
Proud to be a 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))
@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!
@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!
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:
@Anonymous ,
You can create column like
OfferSalesDays = round(divide(ABS(DATEDIFF(SalesDate, OfferExpirationDate, DAY)),30),0) & " month"
and use that in slicer
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
86 | |
83 | |
64 | |
49 |
User | Count |
---|---|
127 | |
108 | |
87 | |
70 | |
66 |