Dax

Hi Everyone,

I have two tables Sales & Calendar. Sales contain columns 'customerName', 'salesDate', and 'productAmount'. While the calendar contains all the dates.

I have created a new column named 'customerType' (Using DAX). I categorize Customers as 'New', 'Active', 'Inactive', and 'Lost'.

The definition of these is:

1. New: Customer who has done first purchase
2. Active: After 1st purchase all the other purchases have been done by the customer.
3. Inactive: If the customer has not made any purchases after 30 days of 'Active' status until the next 'Active' status comes.
4. Lost: If the customer has not made purchases for 60 days.

I have done this by creating a measure on that newly created column.

(Spanish to English)

Table:

Ventas = Sales

Column

fecha = date,

Cliente = Client/Customer

The Below image is of Newly Created Column

Measure is

I have also created another measure to get the count of active/New/Inactive/Lost Customers.

Now the problem is that when I go to get the Lost customer the count of the Data is Incorrect.

Count Lost =

VAR MaxDate = MAX(Calendario[Date])

RETURN

    CALCULATE(

        IF(Ventas[Client Type Measure] = "Lost", 1),

        Calendario[Date] = MaxDate

    )

 

Have created another measure on this (For Non-additive measures)

Count Total Lost = SUMX(VALUES(Ventas[Cliente]),[Count Lost])

 

 

I have created all the measures in the 'Ventas'/'Sales' table and showing the data in a table like that in which

The first three columns from the Calender table and all others the measured from the 'Sales' table.

Note that the joining condition between 'Sales' and 'Calendar' is date and Calender contains all the Dates but Sales contains date where the data is available.