Customer Value for first 1, 3, 6 and 12 Months DAX Help

connortapp · August 13, 2019, 11:08am

Hi All,

I’m looking to find out what a customer spends in the first 1, 3, 6 and 12 months after their first order. I have created a calculated column within the customer table to find out what the first order date is (First Order Date = MIN(Orders[Order Date]). Now I’m struggling how to write the DAX to work out the future sales from this date.

I need to create a calculation which uses the first order date then calculates the total sales for one month after the first order date, then 3 months, 6 and 12.

Any assistance would be greatly appreciated

JarrettM · August 13, 2019, 11:13am

Please post some sample data so we can better assist. More than likely will convert your calculated column to a measure, and then use it to create the others that are needed. Watch some of Sam’s content here on the site regarding measures & measure branching.

Thanks

connortapp · August 13, 2019, 12:03pm

Here you go

Thanks

Demo Data.pbix (3.8 MB)

JarrettM · August 13, 2019, 2:11pm

Here is solution I came up with. let me know what you think. Here are the measures I came up with:

Order Total = 
SUM ( Orders[Order Total] )

First Order Date = 
MIN ( Orders[Date] )

1 Month = 
CALCULATE (
    [Order Total],
    DATESINPERIOD ( Dates[Date], [First Order Date], 1, MONTH )
)

3 Month = 
CALCULATE (
    [Order Total],
    DATESINPERIOD ( Dates[Date], [First Order Date], 3, MONTH )
)

6 Month = 
CALCULATE (
    [Order Total],
    DATESINPERIOD ( Dates[Date], [First Order Date], 6, MONTH )
)

1 Year = CALCULATE([Order Total],DATESINPERIOD(Dates[Date],[First Order Date],1,YEAR))

Here is photo of what it looks like in a table:

Thanks