Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

New coustmer count

"Firstly i explain about my data i have data till 2016 to today  table -cube target   this is my data table here sold to partner code is dealer and invoice quantity is sale my date start from 2016 ...
  • johnt75's avatar
    johnt75
    3 years ago

    The below measure assumes that there is a relationship from your calendar table to the fact table

    Num new dealers =
VAR EndDate =
    EOMONTH ( MAX ( 'Calendar'[Date] ), -1 )
VAR PrevDealers =
    CALCULATETABLE (
        VALUES ( 'CUBE_TARGET'[D_PARTNER_PARTNER_CODE] ),
        REMOVEFILTERS ( 'Calendar' ),
        'Calendar'[Date] <= EndDate
    )
VAR CurrentDealers =
    VALUES ( 'CUBE_TARGET'[D_PARTNER_PARTNER_CODE] )
RETURN
    COUNTROWS ( EXCEPT ( CurrentDealers, PrevDealers ) )
johnt75's avatar
johnt75
Icon for Super User rankSuper User
3 years ago

The below measure assumes that there is a relationship from your calendar table to the fact table

Num new dealers =
VAR EndDate =
    EOMONTH ( MAX ( 'Calendar'[Date] ), -1 )
VAR PrevDealers =
    CALCULATETABLE (
        VALUES ( 'CUBE_TARGET'[D_PARTNER_PARTNER_CODE] ),
        REMOVEFILTERS ( 'Calendar' ),
        'Calendar'[Date] <= EndDate
    )
VAR CurrentDealers =
    VALUES ( 'CUBE_TARGET'[D_PARTNER_PARTNER_CODE] )
RETURN
    COUNTROWS ( EXCEPT ( CurrentDealers, PrevDealers ) )