Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to 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.)
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
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.)
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.
@Bian wrote:
I get correct Count but not correct month.
What does this mean? I didn't write a measure to return a month.
True, I will mark this as complete and be forever greatful 🙂
Anyway you solved my main issue. Many thanks.
Can you give some example data and desired output? It's hard to follow exactly what you're asking for in the abstract.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
69 | |
35 | |
21 | |
18 | |
15 |
User | Count |
---|---|
127 | |
29 | |
27 | |
24 | |
22 |