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.
I would like to know the Number of months in which a customer has placed an order over the passed 12 months or since their [first order month/date] if more recent than 12 months past
If the Customers [first Order month/date] is on or after 12 months in the past of [Current Order Month/Date] then the "start date" will be the [first Order month/date] -- so if [Current order month/date] is Feb 2020 and[first Order month/date] = Mar 2019 then "start date" = [first Order month/date]
if the [first Order month/date] is before 12 months in the past of [Current Order Month/Date] then the "start date" will be 12 months before [current order month/date] --- so if [Current order month/date] is Feb 2020 and[first Order month/date] = Feb 2019 then "start date" = March 2019
Example of results
1) Current Order Month = Feb 2020 and First Order month = Feb 2020 then the number of months where an order was placed = 1
2) Current Order Month = Feb 2020 and First Order month/date = Dec 2019 and the customer placed an order in Dec 2019 and Feb 2020 then the number of months where an order was placed = 2
3) Current Order Month = Feb 2020 and First Order month/date = Dec 2017 then "start date"= March 2019 and the customer placed an order in Mar 2019, Jun 2019, Jul 2019, Dec 2019 and Feb 2020 then the number of months where an order was placed = 5
I have a customer table and a sales table.
Thank you for any help
Hi @belmore ,
Is this problem solved?
If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If not, please let me know.
Best Regards
Icey
Hi @belmore ,
Based on your description, I created an example. Please check if this is what you want:
1. Enter two tables.
2. Create relationship.
3. Create a Measure.
Order Number =
VAR FirstOrderDate =
CALCULATE (
MIN ( Sales[Order Month/Date] ),
ALLEXCEPT ( Sales, Sales[CustomerID] )
)
VAR FirstOrderMonth =
DATE ( YEAR ( FirstOrderDate ), MONTH ( FirstOrderDate ), 1 )
VAR CurrentOrderMonth =
DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
VAR LastYearDate =
EOMONTH ( CurrentOrderMonth, -12 )
VAR LastYearMonth =
IF (
MONTH ( LastYearDate ) = 12,
DATE ( YEAR ( LastYearDate ) + 1, 1, 1 ),
DATE ( YEAR ( LastYearDate ), MONTH ( LastYearDate ) + 1, 1 )
)
VAR StartDate =
IF ( FirstOrderMonth >= LastYearMonth, FirstOrderMonth, LastYearMonth )
RETURN
CALCULATE ( COUNTROWS ( Sales ), Sales[Order Month/Date] >= StartDate )
Then, you can get this:
For more details, please check the attached PBIX file.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Note: A customer may place more than one order within a month
Current Order Month = Feb 2020 and First Order month/date = Dec 2019 and the customer placed an order in Dec 2019(10 orders) and Feb 2020(3 orders) then the number of months where an order was placed = 2
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |