Find the number of months the customer has placed an order

[belmore]

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

[Icey]

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

[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.

[belmore]

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