cumulative account on the line

[Anonymous]

Hi everyone,

I got one more problem...

I got one table with my customer. Each one has a starting date (start to be a customer) and an ending date (stop to be our customer).

I would like the number of customer month by month!

For sure, I got one date table with format AAAA_MM (and others).

Exemple :

Customer      Start              End

blabla           02/05/2018    

blabla2         04/07/2018   05/08/2018

blabla3         12/08/2018   10/10/2018

blabla4         13/09/2018

So, I have to get this :

Month        Nb_Customer

01/2018     0

02/2018     0

03/2018     0

04/2018     0

05/2018     1

06/2018     1

07/2018     2

08/2018     2

09/2018     3

10/2018     2

Thank you for all your support for many times,

[v-yulgu-msft]

Hi @Anonymous,

Please first create a calendar table:

Dim date = FILTER( CALENDAR(DATE(2018,1,1),DATE(2018,12,31)),DAY([Date])=1)

Create a calculated table with below formula:

Result Table = 
VAR temp =
    ADDCOLUMNS (
        CROSSJOIN ( Test3, 'Dim date' ),
        "check", IF (
            ( [Date].[Year] >= Test3[Start].[Year]
                && [Date].[MonthNo] >= Test3[Start].[MonthNo] )
                && (
                    Test3[End] = BLANK ()
                        || ( [Date].[Year] <= Test3[End].[Year]
                        && [Date].[MonthNo] < [End].[MonthNo] )
                ),
            1,
            0
        )
    )
RETURN
    GROUPBY (
        temp,
        [Date].[Year],
        [Date].[MonthNo],
        "Nb_Customer", SUMX ( CURRENTGROUP (), [check] )
    )

Best regards,

Yuliana Gu

[Anonymous · Accepted solution]

Ok,

sorry for my delay

I did that and it's working :

Nb_Adhésions_cumulées = CALCULATE(CALCULATE(
COUNT( BI_TIERS[Num_Tiers] );
FILTER( ALL(BI_TIERS);
COUNTX( FILTER( BI_TIERS; EARLIER( BI_TIERS[AAAA_MM_DebutAdhesion] ) <= BI_TIERS[AAAA_MM_DebutAdhesion] );
BI_TIERS[AAAA_MM_DebutAdhesion]));
BI_TIERS[AAAA_MM_DebutAdhesion]<>"";
USERELATIONSHIP(BI_TIERS[AAAA_MM_DebutAdhesion];BI_DATE[Année_Mois]))
-
CALCULATE(
COUNT( BI_TIERS[Num_Tiers]);
FILTER( ALL(BI_TIERS) ;
COUNTX( FILTER( BI_TIERS; EARLIER( BI_TIERS[AAAA_MM_FinAdhesion] ) <= BI_TIERS[AAAA_MM_FinAdhesion] );
BI_TIERS[AAAA_MM_FinAdhesion]));
BI_TIERS[AAAA_MM_FinAdhesion]<>"";
USERELATIONSHIP(BI_TIERS[AAAA_MM_FinAdhesion];BI_DATE[Année_Mois]));
Filter(ALL(BI_DATE);BI_DATE[Année_Mois]<=max(BI_DATE[Année_Mois])))

with "Fin d'adhésion" the endind date and "Debut Adhésion" the starting date and "Num_Tiers" the n°customer.

Thank you for all,