Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Need Dax Support

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=112076807681614280366&rtpof=true&sd=true 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

     

    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.

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    Anonymous , You can follow approch

     

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

     

    Customer Retention Part 5: LTD Vs Period Retention
    https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-5-LTD-and-PeriodYoY-Retention-is-only/ba-p/2114497

     

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

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

     

    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.