Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |