Customers who purchased certain product over a certain period of time

[gbarr12345]

Hi everyone,

I'm looking to create a measure that shows the customers who purchased a certain period over a certain period of time.

I have a measure here that seems to work for me but in this measure all the information comes from one table but this time roud the data is coming from various tables.

The tables I have are as follows - 

Dimension_Customer - which has the Customer name field (Description).

Dimension_Period which has the field FYPeriod and is written as 202301 for Jan 2023, 202303 for March 2023, etc.

Dimension_Item which has the field Item Description.

Can anybody help me implement these tables into my code below please in order to get it to work?

The measure I have that works is below. I just am struggling to find a way to implement the above tables and fields into it as the below tables etc were from sample data which was a lot easier.

I have attached sample data and expected result as well for your reference.

Many Thanks in advance.

VAR OfficeSuppliesCustomersJanuary2015 =

CALCULATETABLE(

    VALUES(Orders[Customer Name] ),

    FILTER(

        Orders,

        Orders[Product Category] = "Office Supplies" &&

        Orders[Order Date] >= DATE(2015, 1, 1) &&

        Orders[Order Date] <= DATE(2015, 1,31)

    )

)

RETURN

COUNTROWS(

    EXCEPT(

        OfficeSuppliesCustomersJanuary2015,

        CALCULATETABLE(

            VALUES(Orders[Customer Name]),

            FILTER(

                Orders,

                Orders[Product Category] = "Office Supplies" &&

                Orders[Order Date] < DATE(2015, 1, 1)

            )

        )

    )

) = 1,

“Yes”

)

[danextian]

Please post a workable sample data, not an image.

[gbarr12345]

Please find the sample data below:

Dimension_Customer Table

Customer Description

A

B

C

D

E

Dimension_Period Table
FY_Period
20240301
20240302
20240303
20240304
20240305
20240306
20240307
20240308
20240309
20240310
20240311

Dimension_Item Table
Item Description
1
2
3
4
5
6

[danextian]

Please post a sample fact table as well containg the foreign keys and sample transactions. Example: Transaction in this  period at this amount by customer x for this particular item.

[gbarr12345]

By the above do you mean that I need to create a new table that shows how many users etc bought that certain period and how many they purchased etc? Sorry just to get a better understanding of your ask above.

You see in the data I have all of that information above come from different tables so I am trying to find a measure that brings them all together.

Any more help would be greatly appreciated!

[v-yilong-msft]

Hi @gbarr12345 ,

I think you can create Index columns in every table.

Then you can go to model view.

Finally you will get what you want.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gbarr12345]

Hi Yilong,

For the index columns, what code will I input in to them  to make them or does it matter?

Thank you for your response.

[v-yilong-msft]

Hi @gbarr12345 ,

As it stands right now looking down your three tables, there is no direct connection between them, which leads to no way to splice them together directly, so you can use the Index column to relate them to each other.

You can go to Power Query and select the Add column, click on the Index Column.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gbarr12345]

Hi Yilong,

I'm trying that but seem to be getting the error in the screenshot below.

Any idea why this is happening?

[v-yilong-msft]

Hi @gbarr12345 ,

 Firstly you need to make sure the URL http://www.superdatascience.com/api/trim?model=XYZ is correct. Check for any typos or mistakes in the URL.

Ensure that the web service at the given URL is operational. You can do this by trying to access the URL directly in your web browser. 

If the web service requires an API key or other authentication methods, ensure that you have included these in your request.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.