Semi-additive max date

[Bian]

Hi,

I have used Alberto and Marcos DAX pattern for Semi-additive calculations

Semi-additive calculations – DAX Patterns
Primaraly the DAX code to calculate Balance per customer.

I need to add complexity but dont really know how.

I would like to add Product and another date column: EndDate. (The last date the customer can use that product).

I would now like to find the last Balance[Date] for each Product per customer. And from that list find the last EndDate for the Customer.
From that information would I then need to calculate how many customers with an EndDate in that particular month.

Thats my goal: Lost Customers per month

Can i nestle another calculatetable in the first one?

Do I repeat the first variables with EndDate instead?

Is it possible to solve in the filter section of the Calculate(Max()?

Example from Alberto and Marcos:    

Balance LastDateByCustomer :=
VAR MaxBalanceDates =
    ADDCOLUMNS (
        SUMMARIZE (            -- Retrieves the customers
            Balances,          -- from the Balances table
            Customers[Name]
        ),
        "@MaxBalanceDate", CALCULATE (     -- Computes for each customer
            MAX ( Balances[Date] )         -- their last date 
        )
    )
VAR MaxBalanceDatesWithLineage =
    TREATAS (                  -- Changes the lineage of MaxBalanceDates
        MaxBalanceDates,       -- so to make it filter
        Customers[Name],       -- the customer name
        'Date'[Date]           -- and the date
    )
VAR Result =
    CALCULATE (
        SUM ( Balances[Balance] ),
        MaxBalanceDatesWithLineage
    )
RETURN
    Result

The EndDate can change so a later change can set the EndDate to earlier than before. It's therefore not possible to just look att max endDate.

[Bian]

Allright so here is an example from the table

So its a Subscription model and this is the stat-table holding all changes to every subscription. It's currently around 400K rows. 

ChangeDate	CustomerID	SubscriptionID	ProductID	StartDate	EndDate
01-01-2021	11	1	55	01-01-2009	10-30-2021
02-10-2021	11	1	55	01-01-2009	10-30-2022
10-12-2021	11	1	55	01-01-2009	10-30-2021

I wan't to calculate Total Customers with an EndDate in every month. But I only wan't the last change.
Above example should amount to 1 customer in october 2021.

Grateful for any assistance

[AlexisOlson · Accepted solution]

So something like this?

Customers Lost =
VAR EndDate = DATE ( 10, 30, 2021 ) // Or from filter context
VAR MaxChangeDates =
    ADDCOLUMNS (
        SUMMARIZE ( Subscription, Customers[Name] ),
        "@MaxChangeDate", CALCULATE ( MAX ( Subscription[ChangeDate] ) )
    )
VAR AddEndDateCol =
    ADDCOLUMNS (
        MaxChangeDates,
        "@MaxEndDate",
            CALCULATE (
                MAX ( Subscription[EndDate] ),
                TREATAS ( { @MaxChangeDate }, 'Date'[Date] )
            )
    )
RETURN
    COUNTROWS ( FILTER ( AddEndDateCol, [@MaxEndDate] = EndDate ) )

( ^^ Not tested but hopefully gives an idea for the logic, at least.)

[Bian]

This was so helpful! Thank you very much.

I get correct Count but not correct month.

Count is now shown when the change was made. I wan't it to show in the month of the EndDate.

Not realy sure if i should try to change te linage somewhere in the end or if the change should be made in the base.

My Date Table is related to ChangeDate.

[AlexisOlson]

---

@Bian wrote:

I get correct Count but not correct month.

---

What does this mean? I didn't write a measure to return a month.

[Bian]

True, I will mark this as complete and be forever greatful 🙂

Anyway you solved my main issue. Many thanks.

[AlexisOlson]

Can you give some example data and desired output? It's hard to follow exactly what you're asking for in the abstract.