topic Re: Semi-additive max date in DAX Commands and Tips

Semi-additive max date

Bian — Mon, 20 Dec 2021 21:29:55 GMT

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.

Re: Semi-additive max date

AlexisOlson — Mon, 20 Dec 2021 21:51:03 GMT

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

Re: Semi-additive max date

Bian — Tue, 21 Dec 2021 16:20:24 GMT

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

Re: Semi-additive max date

AlexisOlson — Tue, 21 Dec 2021 18:10:23 GMT

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.)

Re: Semi-additive max date

Bian — Tue, 21 Dec 2021 20:47:26 GMT

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.

Re: Semi-additive max date

AlexisOlson — Tue, 21 Dec 2021 20:56:56 GMT

Bian wrote:

I get correct Count but not correct month.

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

Re: Semi-additive max date

Bian — Tue, 21 Dec 2021 21:01:09 GMT

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

Anyway you solved my main issue. Many thanks.