Summing YTD total acquisitions based on a new customer definition

[Stoned_Edge10]

Hi guys,

Is there a simple way to sum up these values in a chart to get YTD, ignoring the fact that it's a measure? I just want to take the raw values displayed in the chart and then sum them up to end up with a summation. This is probably a lot more complex than I am thinking, but I can't seem to get it to work without having the filters intervene. 

What I tried was this below, but it failed horribly and ended up giving me entirely different values to what I had envisioned. 

New Acquisitions YTD = 
CALCULATE (
    [New Venues],
    FILTER (
        ALL ( DIM_Date ),
        'DIM_Date'[Date] <= MAX ( DIM_Date[Date] )
    )
)

Here is how I am calculating the number of new venues per week. It searches the table to see if the client ID existed before a 365 day prior window. If no, it counts as a new customer. 

New Venues = 
VAR CustomerTM =
    VALUES ( FACT_Weekly_Actuals[ClientID] )
VAR PriorCustomers =
    CALCULATETABLE (
        VALUES ( FACT_Weekly_Actuals[ClientID] ),
        FILTER (
            ALL ( 'DIM_Date' ),
            'DIM_Date'[Date]
> MIN ( 'DIM_Date'[Date] ) - 365
                && DIM_Date[Date] < MIN ( DIM_Date[Date] )
        )
    )
RETURN
    CALCULATE (
        SUM( FACT_Weekly_Actuals[Value] ),
        FILTER (
            FACT_Weekly_Actuals,
            FACT_Weekly_Actuals[ClientID] IN EXCEPT ( CustomerTM, PriorCustomers )
        )

I want to visualize 1 + 4 + 2 + 5 + 2 + 16 + ... + n in a simple chart, that shows the YTD total acquisitions we have seen thus far this year. Is it simple or am I overlooking something?

Appreciate your help!

[ValtteriN]

Hi,

For YTD I recommend trying DATESYTD. Here is an example of this:

data:

(note that I have a 1:n relationship between this table and my calendar table)

DAX:

YTD_example = CALCULATE(SUM(SimpleYTD[Value]),DATESYTD('Calendar'[Date]))

End result:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[daXtreme · Accepted solution]

@Stoned_Edge10 

Your problem stems from the fact that when you calculate new customers in some period of time, say 1 week, it'll in general not be the same as calculating new customers over any subset of the period or any superset of the period. To get YTD the way you want you have to iterate the periods you have selected. Here's one way to do it:

// measure 1
[Total Value] = SUM( FACT_Weekly_Actuals[Value] )

// measure 2
// This measure applies to any time frame but you
// have to be careful when you interpret this measure
// as its value ALSO depends on the length of the
// period currently selected (hence your problems with
// YTD).
[New Venues] =
var VisibleCustomers = VALUES( FACT_Weekly_Actuals[ClientID] )
var MinDateVisible = MIN( Dim_Date[Date] )
VAR PriorCustomers =
    CALCULATETABLE(
        VALUES( FACT_Weekly_Actuals[ClientID] ),
        DATESBETWEEN(
            Dim_Date[Date],
            MinDateVisible - 1,
            MinDateVisible - 365
        ),
        // This line is redundant if
        // Dim_Date is marked as Date Table.
        ALL( Dim_Date )
    )
RETURN
    CALCULATE(
        [Total Value],
        EXCEPT(
            VisibleCustomers,
            PriorCustomers
        )
    )
    
// measure 3
// For this measure to work you have to decide
// on the granularity of periods. Let's say
// you want to have YTD and your periods are
// weeks. WeekID must be a sequential identifier
// of weeks.
[New Venues PER WEEK YTD] =
var WeeksToIterateOver =
    CALCULATETABLE(
        VALUES( Dim_Date[WeekId] ),
        DATESYTD( Dim_Date[Date] ),
        // Same remark as before applies...
        ALL( Dim_Date )
    )
var Result = 
    CALCULATE(
        SUMX(
            WeeksToIterateOver,
            [New Venues]
        ),
        REMOVEFILTERS( Dim_Date )
     )
return
    Result

By the way, you should never put a full table (especially the fact table) in any filters that are then used in CALCULATE(TABLE). This is one of the worst practices and even on moderate models will lead to slow DAX, many a time to wrong results and you'll not even be able to diagnose them.

[Stoned_Edge10]

When you say sequential week number, do you mean that it resets after each year (i.e 52 becomes 1 or should 52 continue to 53?) If the latter, I am not sure how to generate this continuing week number calculation in DAX. Oh nice, it needed to be truly sequential (i.e 1 to n number of max weeks in the date calendar). You were right! Thanks so much!

[daXtreme]

@Stoned_Edge10 

Yes, when I say "sequential identifier of the weks", it means exactly that: UNIQUE IDENTIFIER. No two months can have the same number.

[Stoned_Edge10]

Hi there,

That unfortunately didn't work for me. Am I able to send you a segment of my dataset via DM for help? It's a bit hard to try and solve this problem without looking at the underlying data. My sequential week identifier is calculated in the calendar table simply by passing WEEKNUM ( Dim_Date[Date] ) into a calculated column for years 2021 and 2022, for the range of 2021/01/01 to 2022/12/31. 

Regardless, here is the results I get with your above formulas. The number is still far too large. 

Formulas used:

New Venues YTD = 
var WeeksToIterateOver =
    CALCULATETABLE(
        VALUES( DIM_Date[WeekNumberYear]),
        DATESYTD( Dim_Date[Date] ),
        // Same remark as before applies...
        ALL( Dim_Date )
    )
var Result = 
    CALCULATE(
        SUMX(
            WeeksToIterateOver,
            [New Venues Acquisitions]
        ),
        REMOVEFILTERS( Dim_Date )
     )
return
    Result

Total Venues = SUM ( FACT_Weekly_Actuals[Value] ) 

New Venues Acquisitions = 
var VisibleCustomers = VALUES( FACT_Weekly_Actuals[ClientID] )
var MinDateVisible = MIN( Dim_Date[Date] )
VAR PriorCustomers =
    CALCULATETABLE(
        VALUES( FACT_Weekly_Actuals[ClientID] ),
        DATESBETWEEN(
            Dim_Date[Date],
            MinDateVisible - 1,
            MinDateVisible - 365
        ),
        // This line is redundant if
        // Dim_Date is marked as Date Table.
        ALL( Dim_Date )
    )
RETURN
    CALCULATE(
        [Total Venues],
        EXCEPT(
            VisibleCustomers,
            PriorCustomers
        )
    )