Forum Discussion

SachinNamdeo-20's avatar
SachinNamdeo-20
Icon for Helper II rankHelper II
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 so i mention it everywhere."


        i want to create a measure that  calculate new dealer count that if i select november month of 22 then the created measure check all my data table and give me only count of those dealer that was not present in  my table from 2016 to oct month it means it only return the new dealer count which was not present in all previous month as similar 
when i select oct month of 22 it returns that new coustmer count that was not present in all data from 2016 to sep 2022 

 

  • 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 ) )

1 Reply

  • 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 ) )