Need Dax Support

[SAMANTHKUMAR]

Dear Sir,

Need to calculate Total Load & How many vehicles Newly added & Lost

Actually i want to calculte the Total Vehicle During FY based on Bill Date alongwith how many new Vehicles were add based on first vist date & how many vehicles lost in the same period using last visit date.

Thanking you

Samanth Kumarhttps://docs.google.com/spreadsheets/d/1sBk9MkoIQB2vxgVxJ1JTwzt4m_yfgnzp/edit?usp=drive_link&ouid=11... 

[v-kongfanf-msft · Accepted solution]

Hi @SAMANTHKUMAR ,

You can try formula like below:

Newly Added Vehicles = 
VAR FiscalYearStart =
    DATE ( YEAR ( MIN ( 'Table'[Bill Date] ) ), 4, 1 ) 
VAR FiscalYearEnd =
    DATE ( YEAR ( MIN ( 'Table'[Bill Date] ) ) + 1, 3, 31 )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        'Table'[First_Visit_Date] >= FiscalYearStart
            && 'Table'[First_Visit_Date] <= FiscalYearEnd
    )

Lost Vehicles = 
VAR FiscalYearStart =
    DATE ( YEAR ( MIN ( 'Table'[Bill Date] ) ), 4, 1 )
VAR FiscalYearEnd =
    DATE ( YEAR ( MIN ( 'Table'[Bill Date] ) ) + 1, 3, 31 )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        'Table'[Last_Visit_Date] >= FiscalYearStart
            && 'Table'[Last_Visit_Date] <= FiscalYearEnd
    )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@SAMANTHKUMAR , You can follow approch

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...

Customer Retention Part 5: LTD Vs Period Retention
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-5-LTD-and-PeriodYoY-Retentio...

measures

//Only year vs Year, not a level below

This Year = CALCULATE([Total],filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE([Total],filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

Lost Customer This Year = Sumx(VALUES(Customer[Customer Id]),if(ISBLANK([This Year]) && not(ISBLANK([Last Year])) , 1,BLANK()))
New Customer This Year = sumx(VALUES(Customer[Customer Id]), if(ISBLANK([Last Year]) && not(ISBLANK([This Year])) ,1,BLANK()))
Retained Customer This Year = if(not(ISBLANK([This Year])) && not(ISBLANK([Last Year])) , 1,BLANK())