Power BI : DAX: Running Sum with fixed start date - even when filtering

0
votes

I have two tables, with:

  1. Entrydate, several categories
  2. ChurnDate, several categories

The categories are connected via different tables, and the dates are connected with a Calendar.

Now I want to calculate how many customers I have. So I have following DAX formulas

1. SumChurn = 
CALCULATE(
    SUM('kuendigungen'[KUENDIGUNG]);
    FILTER(
        ALLSELECTED('Calendar'[Date]);
        ISONORAFTER('Calendar'[Date]; MAX('Calendar'[Date]); DESC)
    )
)

2. SumEntry = 
CALCULATE(
    SUM('eintritt'[NEUMITGLIED]);
    FILTER(
        ALLSELECTED('Calendar'[Date]);
        ISONORAFTER('Calendar'[Date]; MAX('Calendar'[Date]); DESC)
    )
)

3. TotalCustomers = SumEntry - SumChurn

This works, but in my diagram I want to filter the dates, so that it only visualizes 2020 or the last 3 years.When I do this the calculation is wrong because it only counts in this interval.

Is there a solution that I can filter the date in my visuals but in my calculation the start date of the cummulative sum is always fixed?

I dont't want a new column because I still want to filter my categories of customers...

Thanks, Michaela

Edit: Try to explain it clearer

Example Table 1: contains new customers

Date        unique_id1  unique_id2 unique_id3   cat1 cat2 cat3 cat4 cat5 cat6
1886-02-01  2070030124  550261     207000152145 207  0    0     1   0    0
1887-01-01  4350002756  4081878    435000010707 435  0    0     1   0    0
1888-01-01  7030000597  3206858    703000001279 703  0    0     1   0    0 
1888-06-01  7030016696  3208056    703000005002 703  0    0     1   0    0
1888-09-01  8210024182  204124     821000008664 821  1    0     1   0    1
1889-01-01  7050055324  1988250    705000018309 705  1    0     1   0    0
1889-01-01  8250000278  439485     825000600296 825  0    0     1   0    0
1889-05-01  7030023754  3208355    703000000884 703  0    0     1   0    0
1889-10-01  2110071206  2849359    211000330019 211  0    1     1   0    0
1889-10-01  2110071236  2851371    211000120014 211  0    0     1   0    0
1889-11-14  5190529889  4260192    519000123846 519  1    0     1   0    0
1890-07-01  7330349030  4819467    733000013102 733  0    0     1   0    0
1890-07-01  7330152914  4817492    733000075604 733  1    0     1   0    1
1890-07-01  8190000889  486170     819000215708 819  0    0     1   0    0
1890-07-01  8190444976  486199     819000215740 819  0    0     1   0    0
1890-12-01  8190001388  476049     819000100005 819  0    0     1   0    0
1891-01-01  7030001248  3206975    703000000043 703  0    0     1   0    1

Example Table 2: contains leaving customers similiar to table 1

Example Calendar Table:

01.01.1990
02.01.1990
03.01.1990 ... (till today)

Output shut be a measure

for each day in calendar: number of customer at this date = cumulative_sum(newcustomer) - cumulative_sum(churncustomer)

I get exactly this output, when I run the calculations I wrote, but I want the measure in a way, ehen I filter the date, the sum is still the cummulative sum from the very first date, otherwise the numbers are wrong.

Edit3:

I did exactly the same thing, as mkrabbani posted, but it doesnt't work for me, following calculations:

TotalKuendigungen = 
CALCULATE(
    SUM('kuendigungen'[KUENDIGUNG]);
        FILTER (
            ALL ( 'Calendar'[Date] );
           ( 'Calendar'[Date]   <= MAX (  ( 'Calendar'[Date]  ))
)))

TotalNeukunden = CALCULATE(
    SUM('eintritt'[NEUMITGLIED]);
        FILTER (
            ALL ( 'Calendar'[Date] );
           ( 'Calendar'[Date]   <= MAX (  ( 'Calendar'[Date]  ))
)))

AnzahlMitglieder = [SummeNeumitglied] - [SummeKuendigung]

This is how it looks for me: (Neukunden: new customers, kündigungen: leaving, aktuellemitglieder: number of customers)

Picture 1 correct calculation

Picture 2: also correct calculation, but filter doesnt work

1
powerbidax
can you add some sample data with your expected output? – mkRabbani
i don't understand whats unclear, but i will write an example... – Michaela Spiegel
Example always makes thing clearer than flat explanation :) – mkRabbani
ok, i wrote some example of the table, but feel like just repeating my questions, what exactly is unclear? – Michaela Spiegel

1 Answers

0
votes

thanks for adding some sample data with more explanation. If I get your requirement correct, this below steps with explanation will help you solving your issue I hope.

Assumption: If my understanding is correct, you have 3 tables with Date, new_customer and leaving_customer and they are related as below diagram shown.

enter image description here

Now, I have created some sample data for 10 days, to visualize your requirement/issue. Hope, cumulative counts in the below table is correctly calculated (using basics of cumulative calculation).

enter image description here

At this stage, you need a measure that will calculate current number of customer for each row based on calculation > "cumulative_new_customer - cumulative_leaving_customer" which is not a tough job for you.

But, you are having issue when you are slicing your data using Date slicer. If you are selecting date number 5, which is "January 05 2020" in my sample data. You wants the final counts based on date January 01 to 05, but you are getting only counts from one single date "January 05 2020".

If the above explanation is correct, I would suggest to write 3 separate Measure as explained below in this answer. You can have a look on the output in the below picture I have added with comparison with before and after slicing the data. You can see the number of current user for "January 05 2020" is 41 for both case (Before and After Slicing)

enter image description here

Now, if everything above is meeting your expectation, you can use this below 3 measures as written.

1.

cumulative_new_customer = 
CALCULATE (
    COUNT(new_customer[unique_id]),
    FILTER (
        ALL ( 'Dates'[Date] ),
        'Dates'[Date] <= MAX ( 'Dates'[Date] )
    )
)

2.

cumulative_leaving_customer = 
CALCULATE (
    COUNT(leaving_customer[unique_id]),
    FILTER (
        ALL ( 'Dates'[Date] ),
        'Dates'[Date] <= MAX ( 'Dates'[Date] )
    )
)

3.

number_of_cutomer_today = [cumulative_new_customer] - [cumulative_leaving_customer]

Hope the above details will help you.