number of customer

[Silver75]

Hi

I have my FACTS table with

ID

DATE

PRODUCT

I would like to calculate the number of customer that have bought from first date and last date -365 days, all history except last 365 days. My date field is not contiguous and dateadd doesn't work

Could you help me to wrtite this measure in DAX, please?

Thanky you  

[v-yulgu-msft]

Hi @Silver75,

What did you mean "customer that have bought from first date and last date -365 days"? Please elaborate your requirement with detailed data and screenshot of desired output.

Below is the test based on my assumption.

To work around the limitation of noncontiguous date, we should create a calendar table. And create a calculated column to get the date 365 days ago.

Date table = CALENDAR(DATE(2015,1,1),DATE(2017,12,31))
Last date-365 = DATEADD('Date table'[Date].[Date],-365,DAY)

Data view. [First date], [Last date] and [Last date -365] are calculated columns I added to source table, in order to understand steps clearly. Actually, there is no need to add them to source table.

I used below measure formula to count the customer.

measure =
CALCULATE (
    DISTINCTCOUNT ( FACTS[ID] ),
    FILTER (
        FACTS,
        CALCULATE ( MIN ( FACTS[DATE] ), ALLEXCEPT ( FACTS, FACTS[ID] ) )
            <= LOOKUPVALUE (
                'Date table'[Last date-365],
                'Date table'[Date], CALCULATE ( MAX ( FACTS[DATE] ), ALLEXCEPT ( FACTS, FACTS[ID] ) )
            )
    )
)

The result of this measure is 1.

Regards,
Yuliana Gu

[Silver75]

Hi @v-yulgu-msft,

than you

variabiles in FACTS: ID, DATE; PRODUCT; SALES

each customer ID has an history of sales similar your table without First Date e Last Date column.

Selecting a Year (or period) I need to calculate sales to date, eg Sales in the year in exam,  and all sales histroy except last 365 days from last date order -365 day, that is all order (all customer) from first data to  "my last data"  = (last data real of bought -365 days)

[v-yulgu-msft]

Hi @Silver75,

I have mentioned before, [First date], [Last date] and [Last date -365] are calculated columns I added to source table, in order to understand steps clearly. Actually, there is no need to add them to source table.

According to current description, "(all customer) from first data to  "my last data"  = (last data real of bought -365 days)", so, you want to calculate how many customers has bought over one year, right? If so, based on my original post, we can get this desired number.

If I have something misunderstood, please correct me. Please elaborate your requirement with examples. What is your desired output based on the sample data I provided above?

Regards,
Yuliana Gu

[Silver75]

thank you for your support

I solved with a new dimension in SQL

[v-yulgu-msft]

Hi @Silver75,

Glad to hear that you have achieved your requirement. Thanks for your sharing.

Regards,
Yuliana Gu