cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
belmore
Helper II
Helper II

Find the number of months the customer has placed an order

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

3 REPLIES 3
Icey
Community Support
Community Support

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
Community Support
Community Support

Hi @belmore ,

 

Based on your description, I created an example. Please check if this is what you want:

 

1. Enter two tables.

ex1.PNG

2. Create relationship.

ex2.PNG

 

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:

ex3.PNG

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
Helper II
Helper II

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

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors